mysql - 如何将空集放入内部连接

Question

我是 MySql 的完全初学者，我希望你能帮助我。我想要一个有时间进出的 DTR 用户界面。所以我有一个字段，如 -id_biometrics,empno,datecreated,time_created,status,device 问题是如果员工忘记上类或超时，我想把时间输入或超时“NULL”是我编码的。

SELECT start_log.empno AS "Employee Number", start_log.date_created, start_log.time_created AS "Time In", end_log.time_created AS "Time Out"    
FROM biometrics AS start_log
INNER JOIN biometrics AS end_log ON start_log.empno = end_log.empno
WHERE start_log.status =0
AND end_log.status =1
AND start_log.empno =2
GROUP BY start_log.date_created, start_log.empno

如果他完成当天的上类和下类时间，我在这里的输出是。

Employee Number|date_created |Time_in   | Time_Out
             2 | 2013-07-15  | 11:08:07 | 15:00:00

如果他/她忘记超时或超时，则表明 MySQL 返回了一个空结果集(即零行)。 (查询耗时 0.0016 秒)

我想要的是

Employee Number | date_created |Time_in   | Time_Out
         3      | 2013-07-15   | 11:50:00 | Null

请帮我解决这个问题。我非常需要它，谢谢。

Answer 1

你在找这个吗？

SELECT empno, date_created, 
       MIN(CASE WHEN status = 0 THEN time_created END) time_in,
       MIN(CASE WHEN status = 1 THEN time_created END) time_out
  FROM biometrics
 GROUP BY empno, date_created

示例输出:

+-------+--------------+----------+----------+| empno | date_created | time_in  | time_out |+-------+--------------+----------+----------+|     2 | 2013-07-15   | 11:08:07 | 15:00:00 ||     3 | 2013-07-15   | 11:50:00 | NULL     ||     4 | 2013-07-15   | NULL     | 16:00:00 |+-------+--------------+----------+----------+

Here is SQLFiddle demo

UPDATE If you may have multiple in or out records and you always want to fetch first in and last out then just use MAX() aggregate for time_out

SELECT empno, date_created, 
       MIN(CASE WHEN status = 0 THEN time_created END) time_in,
       MAX(CASE WHEN status = 1 THEN time_created END) time_out
  FROM biometrics
 GROUP BY empno, date_created

这是 SQLFiddle 演示