mysql - 无法在mysql中获取一个月的所有日期

Question

我想制作一个程序来显示一个月内一名员工的出勤情况。如果只显示员工在场的日期，我可以这样做，但我想显示员工不在场(缺勤)的日期。我有这样的 MySQL 查询

SELECT x.Tanggal
        , TIME(a.EventTime) AS TimeIn
FROM
(SELECT  Tanggal FROM (
SELECT DATE_ADD('2014-05-01', INTERVAL n5.num*10000+n4.num*1000+n3.num*100+n2.num*10+n1.num DAY ) AS Tanggal FROM
(SELECT 0 AS num
   UNION ALL SELECT 1
   UNION ALL SELECT 2
   UNION ALL SELECT 3
   UNION ALL SELECT 4
   UNION ALL SELECT 5
   UNION ALL SELECT 6
   UNION ALL SELECT 7
   UNION ALL SELECT 8
   UNION ALL SELECT 9) n1,
(SELECT 0 AS num
   UNION ALL SELECT 1
   UNION ALL SELECT 2
   UNION ALL SELECT 3
   UNION ALL SELECT 4
   UNION ALL SELECT 5
   UNION ALL SELECT 6
   UNION ALL SELECT 7
   UNION ALL SELECT 8
   UNION ALL SELECT 9) n2,
(SELECT 0 AS num
   UNION ALL SELECT 1
   UNION ALL SELECT 2
   UNION ALL SELECT 3
   UNION ALL SELECT 4
   UNION ALL SELECT 5
   UNION ALL SELECT 6
   UNION ALL SELECT 7
   UNION ALL SELECT 8
   UNION ALL SELECT 9) n3,
(SELECT 0 AS num
   UNION ALL SELECT 1
   UNION ALL SELECT 2
   UNION ALL SELECT 3
   UNION ALL SELECT 4
   UNION ALL SELECT 5
   UNION ALL SELECT 6
   UNION ALL SELECT 7
   UNION ALL SELECT 8
   UNION ALL SELECT 9) n4,
(SELECT 0 AS num
   UNION ALL SELECT 1
   UNION ALL SELECT 2
   UNION ALL SELECT 3
   UNION ALL SELECT 4
   UNION ALL SELECT 5
   UNION ALL SELECT 6
   UNION ALL SELECT 7
   UNION ALL SELECT 8
   UNION ALL SELECT 9) n5
) a
WHERE (Tanggal BETWEEN '2014-05-01' AND '2014-05-31')) x
LEFT JOIN VW_AttendanceIN a ON DATE(a.EventTime)=x.Tanggal
WHERE a.EmployeeID='90443'
AND (DATE(a.EventTime) BETWEEN '2014-05-01' AND '2014-05-31')
GROUP BY x.Tanggal

但是，结果只是给出这样的当前日期

结果:

|   Tanggal  |  TimeIn  |
| 2014-05-01 | 08:00:00 |
| 2014-05-02 | 08:00:00 |
| 2014-05-05 | 08:00:00 |
| 2014-05-06 | 08:00:00 |
| 2014-05-07 | 08:00:00 |

第三和第四没有列出我想要这样的结果

|   Tanggal  |  TimeIn  |
| 2014-05-01 | 08:00:00 |
| 2014-05-02 | 08:00:00 |
| 2014-05-03 |   NULL   |
| 2014-05-04 |   NULL   |
| 2014-05-05 | 08:00:00 |
| 2014-05-06 | 08:00:00 |
| 2014-05-07 | 08:00:00 |

谁能帮我解决这个问题？

Answer 1

解决方案

你需要替换这个:

LEFT JOIN VW_AttendanceIN a ON DATE(a.EventTime)=x.Tanggal
WHERE a.EmployeeID='90443'
AND (DATE(a.EventTime) BETWEEN '2014-05-01' AND '2014-05-31')

具有以下内容:

LEFT JOIN VW_AttendanceIN a ON DATE(a.EventTime)=x.Tanggal AND a.EmployeeID='90443'

原因

WHERE 子句中对连接表的引用过滤掉连接表中没有匹配行的最终结果集行，即它不允许 a.EmployeeID 或 a.EventTime 为 NULL。如果将这些引用放在 LEFT JOIN 的 ON 子句中，则情况并非如此，因为它会从连接表中而不是从最终结果集中过滤掉不匹配的行。