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

jQuery野兽

我有这张桌子:

+------------+---------+--------+----------+
| 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-01-01 和 2018-01-09 之间的一切
  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
戈登·利诺夫

这是一个简单的修复:

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;

问题是JOINNOW()NOW()作为时间组件,所以JOIN总是会失败(好吧,除非你正好在午夜运行)。

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

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章

比较两个表,如果值存在显示1,如果值不存在显示0

c# LiveCharts WPF 如何将 0 值添加到数据不存在的日期以在 CartesianChart 中显示

MySQL的日期不存在返回0,算多少行存在一个特定的日期

查询Mysql-对于两个表中都不存在的值,返回0

两个日期之间的 Excel COUNTIFS 不断返回 0

MySQL选择日期,如果不存在日期,则具有预定义的值

如果在MySQL中的group by语句中不存在值,如何显示0个计数?

比较两个日期结果0

MySQL从两个表中选择,获取所有数据,如果不存在,则使num 0

Pandas 生成缺失的日期和小时数,值为 0

选择两个日期之间的日期和值以及累计总和

当两个日期相同的日期时如何将日期差设置为“ 0”

熊猫groupby 0值(如果不存在)

如果设置为 0 的列中不存在值

DAX JOIN(如果值介于两个日期之间)

确定两个日期之间是否存在值

在两个日期之间插入值

隔离一个值变为0的日期,然后从该日期开始聚合另一个值

如果且仅在两列中都存在值,则两个日期列之间的差异

在不存在的日期之间插入累积值:

获取 MySQL 中两个日期之间的值差

Chart.js:当两个日期之间有间隔时,将yAxis指向0

MySQL日期范围(如果数据不可用)显示0

MySQL-将“按日期分组”值的Count值获取为0

MySQL根据START和END日期之间的日期显示值

如果特定日期没有值返回0,如何在mysql中获取最近7天的记录

如果同一表中不存在日期,则 MYSQL 选择具有预定义值的数据

根据公共列合并两个文件;如果变体存在于文件 2 中,则保留该值,并将 0 添加到文件 2 中不存在的那些

基于公共列合并两个文件;为文件 2 中不存在的变体添加 0,并在存在的变体中保留原始值