MySQL:选择两个日期之间的日期值。如果日期/值不存在则显示日期和值 0

Question

我有这张表:

+------------+---------+--------+----------+
| date       | regular | deduct | overtime |
+------------+---------+--------+----------+
| 2018-01-01 | 8       | 0      | 0        |
+------------+---------+--------+----------+
| 2018-01-03 | 8       | 0      | 0        |
+------------+---------+--------+----------+
| 2018-01-04 | 8       | 0      | 1        |
+------------+---------+--------+----------+
| 2018-01-09 | 8       | 2      | 0        |
+------------+---------+--------+----------+

我想选择:

1. 2018 年 1 月 1 日至 2018 年 1 月 9 日之间的所有内容
2. 工时 = 常规 - 扣除 + 加类
3. 如果未找到日期，则显示小时数 = 0

得到以下结果:

+------------+-------+
| date       | hours |
+------------+-------+
| 2018-01-01 | 8     |
+------------+-------+
| 2018-01-02 | 0     |
+------------+-------+
| 2018-01-03 | 0     |
+------------+-------+
| 2018-01-04 | 9     |
+------------+-------+
| 2018-01-05 | 0     |
+------------+-------+
| 2018-01-06 | 0     |
+------------+-------+
| 2018-01-07 | 0     |
+------------+-------+
| 2018-01-08 | 0     |
+------------+-------+
| 2018-01-09 | 6     |
+------------+-------+

这是生成两个日期之间的一系列日期的 SQL，其有效:

SELECT DATE(cal.date) as date
FROM ( 
      SELECT SUBDATE(NOW(), INTERVAL 60 DAY) + INTERVAL xc DAY AS date 
      FROM ( 
            SELECT @xi:=@xi+1 as xc from 
            (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc1, 
            (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc2, 
            (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc3, 
            (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc4, 
            (SELECT @xi:=-1) xc0 
      ) xxc1 
) cal 
WHERE cal.date >= '2018-01-01' and cal.date <= '2018-01-09'
GROUP BY DATE(cal.date)
ORDER BY cal.date ASC

这就是我尝试将上面的表格与没有结果的时间连接起来的方法:

SELECT DATE(cal.date) as date, IFNULL((x.regular + x.overtime - x.deduct), 0) AS hours 
FROM ( 
      SELECT SUBDATE(NOW(), INTERVAL 60 DAY) + INTERVAL xc DAY AS date 
      FROM ( 
            SELECT @xi:=@xi+1 as xc from 
            (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc1, 
            (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc2, 
            (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc3, 
            (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc4, 
            (SELECT @xi:=-1) xc0 
      ) xxc1 
) cal 
LEFT JOIN attendance x ON x.date = cal.date
WHERE cal.date >= '2018-01-01' and cal.date <= '2018-01-09'
GROUP BY DATE(cal.date)
ORDER BY cal.date ASC

Answer 1

这是一个简单的修复:

SELECT DATE(cal.date) as date, IFNULL((x.regular + x.overtime - x.deduct), 0) AS hours 
FROM (SELECT (DATE('2018-01-01') + INTERVAL xc DAY) AS date 
      FROM (SELECT (@xi := @xi + 1) as xc 
            FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) xc1 CROSS JOIN
                 (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) xc2 CROSS JOIN
                 (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) xc3 CROSS JOIN 
                 (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) xc4 CROSS JOIN 
                 (SELECT @xi:=-1) xc0 
           ) xxc1 
     ) cal LEFT JOIN
     attendance x
     ON x.date = cal.date
WHERE cal.date >= '2018-01-01' and cal.date <= '2018-01-09'
GROUP BY DATE(cal.date)
ORDER BY cal.date ASC;

问题在于JOIN 和NOW()。 NOW() 作为时间组件，因此 JOIN 将始终失败(好吧，除非您在午夜运行)。

您可以使用 CURDATE() 修复您的版本，但我发现逻辑很难遵循，查询的一部分中的日期相对于当前，而另一部分中的固定日期。