mysql - 从满足的状态获取间隔为1天的日期，并从数据库查询获取ID

This question already has answers here:

How can I check if the same ID has different values in different rows?
                                
                                    （3个答案）
                                
                        
                        
                            Choosing a column that fulfills many conditions in different records
                                
                                    （2个答案）
                                
                        
                                4个月前关闭。
            
                    
我有一个名为tbl_attendance的表，其中包含这些列和值：

Attendance_id          emp_id          time                   status
     1                14100003     2019-09-09 04:00:00          1
     2                14100003     2019-09-09 12:00:00          2
     3                14100004     2019-09-09 22:00:00          1
     4                14100004     2019-09-10 06:00:00          2
     5                14100005     2019-09-09 23:00:00          1
     6                14100005     2019-09-09 23:11:00          1
     7                14100005     2019-09-10 07:00:00          2
     8                14100005     2019-09-10 07:04:00          2

现在我想得到这样的结果：

5                14100005     2019-09-09 23:00:00          1
7                14100005     2019-09-10 07:00:00          2

从结果来看，我希望这个条件能够得到满足，


员工ID 14100005中有重复的值，但我想获取第一个状态为1和2的值
我需要条件，如果日期没有状态1或2，则查询将不返回任何内容。


我有这个查询，它什么也不返回：

SELECT DISTINCT DATE(t.time) AS time, t.employee_id
FROM tbl_attendance AS t
JOIN tbl_attendance AS t2
     ON DATE(t.time) = DATE(t2.time) AND t.employee_id = t2.employee_id
WHERE t.`status` = 1 AND t2.`status` = 2
  AND DATE(t.time) BETWEEN "2019-09-09" AND "2019-09-10"
  AND t.employee_id = 14100005
ORDER BY t.time

最佳答案

考虑以下：

DROP TABLE IF EXISTS attendance;

CREATE TABLE attendance
(Attendance_id SERIAL PRIMARY KEY
,emp_id INT NOT NULL
,time DATETIME
,status TINYINT NOT NULL
);

INSERT INTO attendance VALUES
(1,3,'2019-09-09 04:00:00',1),
(2,3,'2019-09-09 12:00:00',2),
(3,4,'2019-09-09 22:00:00',1),
(4,4,'2019-09-10 06:00:00',2),
(5,5,'2019-09-09 23:00:00',1),
(6,5,'2019-09-10 07:00:00',2);

SELECT x.*
  FROM attendance x
  JOIN attendance y
    ON y.emp_id = x.emp_id
   AND y.attendance_id <> x.attendance_id
   AND y.status IN(1,2)
   AND y.status <> x.status
   AND y.time >= '2019-09-09 00:00:00'
   AND y.time <= '2019-09-10 06:00:00'
 WHERE x.emp_id = 4
   AND x.status IN(1,2)
   AND x.time >= '2019-09-09 00:00:00'
   AND x.time <= '2019-09-10 06:00:00';
+---------------+--------+---------------------+--------+
| Attendance_id | emp_id | time                | status |
+---------------+--------+---------------------+--------+
|             4 |      4 | 2019-09-10 06:00:00 |      2 |
|             3 |      4 | 2019-09-09 22:00:00 |      1 |
+---------------+--------+---------------------+--------+