如何将SQL查询中未记录的跑步日距离设为0km？

嘿，这个需求我常碰到，很容易搞定！核心思路就是先构建一个包含一周7天的完整日期序列，再和你的跑步数据表做左连接，把没有跑步记录的日期对应的距离自动补成0就行。

具体实现步骤 #

首先，我们需要生成一个代表一周7天的数据集（day1到day7），然后把你的跑步数据按星期几对应到这个数据集上，最后用COALESCE函数把空值替换成0。

以下是针对不同数据库的写法，你可以根据自己用的数据库选择：

1. MySQL（8.0+支持CTE，低版本可用UNION ALL替代）

-- 生成一周7天的序列
WITH days_of_week AS (
    SELECT 1 AS day_num UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7
)
SELECT 
    CONCAT('day', dw.day_num) AS day,
    -- 把空值替换成0，保留原有的距离格式
    CONCAT(COALESCE(t.DISTANCE, 0), 'km') AS distance
FROM days_of_week dw
LEFT JOIN (
    SELECT 
        -- 把Unix时间戳转成星期几，这里假设周一为day1
        CASE DATE_FORMAT(FROM_UNIXTIME(DATE), '%w')
            WHEN 1 THEN 1  -- 周一
            WHEN 2 THEN 2  -- 周二
            WHEN 3 THEN 3  -- 周三
            WHEN 4 THEN 4  -- 周四
            WHEN 5 THEN 5  -- 周五
            WHEN 6 THEN 6  -- 周六
            WHEN 0 THEN 7  -- 周日
        END AS day_num,
        DISTANCE
    FROM your_table
    WHERE WEEK = '201812'  -- 替换成你要查询的周数，比如2018年第12周
) t ON dw.day_num = t.day_num
ORDER BY dw.day_num;

2. PostgreSQL

PostgreSQL生成序列更简洁，用generate_series即可：

WITH days_of_week AS (
    SELECT generate_series(1,7) AS day_num
)
SELECT 
    'day' || dw.day_num AS day,
    CONCAT(COALESCE(t.distance, 0), 'km') AS distance
FROM days_of_week dw
LEFT JOIN (
    SELECT 
        -- 同样假设周一为day1，调整映射可改变day1的起始日
        CASE extract(dow FROM to_timestamp(date))
            WHEN 1 THEN 1
            WHEN 2 THEN 2
            WHEN 3 THEN 3
            WHEN 4 THEN 4
            WHEN 5 THEN 5
            WHEN 6 THEN 6
            WHEN 0 THEN 7
        END AS day_num,
        distance
    FROM your_table
    WHERE week = '201812'
) t ON dw.day_num = t.day_num
ORDER BY dw.day_num;

关键说明 #

• 如果你的一周起始日不是周一（比如周日是day1），只需要修改CASE语句里的映射关系就行，比如把WHEN 0 THEN 1，WHEN 1 THEN 2以此类推。
• COALESCE函数的作用是当原表没有对应记录时，把NULL替换成0，完美实现补全需求。
• 记得把your_table替换成你实际的表名，WEEK的条件也改成你要查询的目标周数。

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