时间戳

首页 > 解决方案 > MySQL显示范围之间的所有日期虽然没有记录

mysql - MySQL显示范围之间的所有日期虽然没有记录

问题描述

我有表 xDateList 包含：

+---------+
 xDateList 
+---------+
2018-11-01
2018-11-02
2018-11-03
2018-11-04
2018-11-05

还有表扫描日志

--------------------------------------
ID  Name   ScanDate               Code
--------------------------------------
1   John   2018-11-02 07:00:00    IN
1   John   2018-11-02 10:00:00    OUT
1   John   2018-11-04 08:00:00    IN
1   John   2018-11-04 12:00:00    OUT

我已经尝试过了，但它无法显示 xDateList 上的所有记录，它只显示表 ScanLog 上的记录

select xDateList.date, 
       scanlog.name, 
       MIN(scanlog.scandate) AS `IN`, 
       MAX(scanlog.scandate) AS `OUT`
from scanlog 
left JOIN xDateList ON xDateList.date = date(scanlog.scandate) 
where scanlog.id='1' 
GROUP BY DATE(scanlog.scandate)

我想要这样的结果

--------------------------------------------
Date         ID   Name   In         Out
--------------------------------------------
2018-11-01   1    John   
2018-11-02   1    John   07:00:00   10:00:00
2018-11-03   1    John
2018-11-04   1    John   08:00:00   12:00:00
2018-11-05   1    John

感谢你们对我的帮助

标签： mysqldate

解决方案

您需要更改LEFT JOIN. 永远记住，为了考虑特定表中的所有行；该特定表应该是联接中最左侧的表。

ON此外，无论何时进行 LEFT JOIN，都应在子句中指定右侧表的条件；否则 WHERE 子句中的条件可以有效地将其转换为 INNER JOIN。

此外，在这种情况下，GROUP BY应该在 上xDateList.date显示与值对应的所有行xDateList.date。而且，我们需要确保列表中的所有非聚合列也在子句SELECT中指定。GROUP BY请检查：在 MySql 中执行查询时与 only_full_group_by 相关的错误

SELECT xDateList.date, 
       scanlog.name, 
       MIN(scanlog.scandate) AS `IN`,
       MAX(scanlog.scandate) AS `OUT`
FROM xDateList  
LEFT JOIN scanlog  
  ON xDateList.date = date(scanlog.scandate) AND
     scanlog.id='1' 
GROUP BY xDateList.date, scanlog.name

结果

| date       | name | IN                  | OUT                 |
| ---------- | ---- | ------------------- | ------------------- |
| 2018-11-01 |      |                     |                     |
| 2018-11-02 | John | 2018-11-02 07:00:00 | 2018-11-02 10:00:00 |
| 2018-11-03 |      |                     |                     |
| 2018-11-04 | John | 2018-11-04 08:00:00 | 2018-11-04 12:00:00 |
| 2018-11-05 |      |                     |                     |

在 DB Fiddle 上查看

推荐阅读