Ich möchte Bedingungen aus 2 verschiedenen Spalten für meine Abfrage kombinieren. Dies ist meine ursprüngliche Abfrage. Sie können es in sqlfiddle.com testen.

-- creating database first for test data
create table attendance(Id int, DateTime datetime, Door char(20));
INSERT INTO attendance VALUES
(    1,   '2016-01-01 08:00:00',  'In'),
(    2,   '2016-01-01 09:00:00',  'Out'),
(    3,   '2016-01-01 09:15:00',  'In'),
(    4,   '2016-01-01 09:30:00',  'In'),
(    5,   '2016-01-01 10:00:00',  'Out'),
(    6,   '2016-01-01 15:00:00',  'In');

SELECT * FROM attendance;
SELECT 
@id:=@id+1 Id,
MAX(IF(Door = 'In', DateTime, NULL)) `Check In`,
MAX(IF(Door = 'Out', DateTime, NULL)) `Check Out`
FROM
(SELECT 
*, 
CASE 
    WHEN
        (Door != 'Out' AND @last_door = 'Out')
    THEN @group_num:=@group_num+1
    ELSE @group_num END door_group, 
    @last_door:=Door
FROM attendance 
JOIN (SELECT @group_num:=1,@last_door := NULL) a
) t JOIN (SELECT @id:=0) b
GROUP BY t.door_group
HAVING SUM(Door = 'In') > 0 AND SUM(Door = 'Out') > 0;

//output
+------+---------------------+---------------------+
| Id   | Check In            | Check Out           |
+------+---------------------+---------------------+
|    1 | 2016-01-01 08:00:00 | 2016-01-01 09:00:00 |
|    2 | 2016-01-01 09:30:00 | 2016-01-01 10:00:00 |
+------+---------------------+---------------------+

Aus der obigen Abfrage möchte ich eine weitere Spalte hinzufügen.

-- creating database first for test data
create table attendance(Id int, DateTime datetime, Door char(20), Active_door char(20));
INSERT INTO attendance VALUES
(    1,   '2016-01-01 08:00:00',  'In', ''),
(    2,   '2016-01-01 09:00:00',  'Out', ''),
(    3,   '2016-01-01 09:15:00',  'In', ''),
(    4,   '2016-01-01 09:30:00',  'In', ''),
(    5,   '2016-01-01 09:35:00',  '', 'On'),
(    6,   '2016-01-01 10:00:00',  'Out', ''),
(    7,   '2016-01-01 16:00:00',  '', 'Off');

Dies sind die Änderungen, die ich an meiner Abfrage vorgenommen habe, aber sie funktionieren nicht.

SELECT * FROM attendance;
SELECT 
@id:=@id+1 Id,
MAX(IF(Door = 'In' OR Active_door = "On", DateTime, NULL)) `Check In`,
MAX(IF(Door = 'Out' OR Active_door = "Off", DateTime, NULL)) `Check Out`
FROM
(SELECT 
*, 
CASE 
    WHEN
        ((Door != 'Out' OR Active_door != "Off") AND (@last_door = 'Out' OR  @last_door = 'Off'))
    THEN @group_num:=@group_num+1
    ELSE @group_num END door_group, 
    @last_door:=Door
FROM attendance 
JOIN (SELECT @group_num:=1,@last_door := NULL) a
) t JOIN (SELECT @id:=0) b
GROUP BY t.door_group
HAVING SUM(Door = 'In') > 0 OR SUM(Active_door = 'On') > 0 AND SUM(Door = 'Out') > 0  OR SUM(Active_door = 'Off') > 0;

//output
+------+---------------------+---------------------+
| Id   | Check In            | Check Out           |
+------+---------------------+---------------------+
|    1 | 2016-01-01 08:00:00 | 2016-01-01 09:00:00 |
|    2 | 2016-01-01 09:35:00 | 2016-01-01 10:00:00 |
|    3 | NULL                | 2016-01-01 16:00:00 |
+------+---------------------+---------------------+

//my desire output
+------+---------------------+---------------------+
| Id   | Check In            | Check Out           |
+------+---------------------+---------------------+
|    1 | 2016-01-01 08:00:00 | 2016-01-01 09:00:00 |
|    2 | 2016-01-01 09:35:00 | 2016-01-01 16:00:00 |
+------+---------------------+---------------------+

Bitte hilf mir Leute, wie kann ich die gewünschte Ausgabe bekommen. Ich möchte in beiden Spalten als Letzter ein- und als Letzter aussteigen. Danke im Voraus