关联日历表补全缺失日期并置对应列值为Null的SQL技术问询

嘿，我来帮你梳理这个日期补全的问题，顺便排查下你现有查询的潜在问题～

核心需求回顾 #

你需要：

• 把业务表和日历表关联，补全业务表中缺失的日期
• 缺失日期对应的Steps列显示为null，而不是其他默认值（比如0）

你现有查询的潜在问题 #

从你给出的片段SELECT sum(steps), date(from_unixtime(u.in_date))...来看，有几个关键问题：

1. 使用sum(steps)不符合需求：如果业务表没有某日期的数据，sum(steps)会返回0而不是null，这和你期望的结果不符。
2. 关联顺序大概率错误：要补全日期，必须用日历表作为左表左关联业务表，这样所有日历日期都会被保留；如果反过来用业务表左联日历，只会保留业务表已有的日期，达不到补全效果。
3. 日期字段的取值逻辑：补全后的日期应该取自日历表，而不是业务表的date(from_unixtime(u.in_date))——否则缺失日期的字段会是空值，而不是正确的日历日期。

正确的SQL写法示例 #

假设你的业务表叫business_table，日历表叫calendar（包含cal_date字段，存储所有需要覆盖的日期），正确的查询应该是：

SELECT
    bt.Steps,
    c.cal_date AS Date
FROM calendar c
LEFT JOIN business_table bt
    ON c.cal_date = bt.Date
-- 可选：限定日期范围，比如你示例中的2018-04-28到2018-04-30
WHERE c.cal_date BETWEEN '2018-04-28' AND '2018-04-30'
ORDER BY c.cal_date DESC;

如果你的业务表日期是Unix时间戳（比如in_date是时间戳字段），只需要调整关联条件：

SELECT
    bt.Steps,
    c.cal_date AS Date
FROM calendar c
LEFT JOIN business_table bt
    ON c.cal_date = DATE(FROM_UNIXTIME(bt.in_date))
WHERE c.cal_date BETWEEN '2018-04-28' AND '2018-04-30'
ORDER BY c.cal_date DESC;

没有现成日历表？临时生成即可 #

如果没有提前创建的日历表，用递归CTE可以快速生成需要的日期范围（以MySQL 8.0+为例）：

WITH RECURSIVE calendar AS (
    -- 起始日期
    SELECT '2018-04-28' AS cal_date
    UNION ALL
    -- 逐天生成直到结束日期
    SELECT DATE_ADD(cal_date, INTERVAL 1 DAY)
    FROM calendar
    WHERE cal_date < '2018-04-30'
)
SELECT
    bt.Steps,
    c.cal_date AS Date
FROM calendar c
LEFT JOIN business_table bt
    ON c.cal_date = bt.Date
ORDER BY c.cal_date DESC;

这个查询会完美输出你期望的结果：

内容的提问来源于stack exchange，提问作者Newbb