MySQL Display All Date between range although is no record

Question

我有表 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

谢谢你帮助我

Answer 1

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

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

此外，在这种情况下，GROUP BY 应该在 xDateList.date 上以显示与 xDateList.date 对应的所有行值。并且，我们需要确保 SELECT 列表中的所有非聚合列也在 GROUP BY 子句中指定。检查:Error related to only_full_group_by when executing a query in MySql

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 |      |                     |                     |

View on DB Fiddle