SQL实现：将月份内不完整周的缺失星期天数补0

嘿，我来帮你解决这个补全缺失星期记录的SQL问题！根据你给出的需求，我们需要给2018年2月的不完整周补上date为0的缺失星期行，下面是几种实用的实现方案，你可以根据自己使用的数据库类型灵活调整：

通用思路说明 #

核心逻辑是：先生成1到7的完整星期数列表，找出2018年2月数据中没有出现过的星期数，把这些缺失的星期数转换成date=0的记录，最后将补全的记录和原表数据合并，并按要求排序（月初缺失行在前，原表数据按日期排序，月末缺失行在后）。

方案1：支持CTE（公共表表达式）的数据库（MySQL 8+/PostgreSQL/SQL Server 2008+等） #

MySQL 8+版本示例 #

-- 生成1-7的完整星期数列表
WITH all_week_days AS (
    SELECT 1 AS week_day UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7
),
-- 提取2018年2月已存在的星期数（去重）
existing_days AS (
    SELECT DISTINCT week_day
    FROM your_table
    WHERE STR_TO_DATE(date, '%d-%m-%Y') BETWEEN '2018-02-01' AND '2018-02-28'
),
-- 生成需要补全的缺失记录（date设为0）
missing_days AS (
    SELECT 0 AS date, awd.week_day
    FROM all_week_days awd
    LEFT JOIN existing_days ed ON awd.week_day = ed.week_day
    WHERE ed.week_day IS NULL
)
-- 合并原表数据与补全记录，并按要求排序
SELECT date, week_day
FROM (
    -- 原表2月的有效数据
    SELECT date, week_day
    FROM your_table
    WHERE STR_TO_DATE(date, '%d-%m-%Y') BETWEEN '2018-02-01' AND '2018-02-28'
    UNION ALL
    -- 补全的缺失记录
    SELECT date, week_day
    FROM missing_days
) combined
ORDER BY 
    -- 排序规则：月初缺失行 → 原表数据 → 月末缺失行
    CASE 
        WHEN date = 0 AND week_day < (SELECT MIN(week_day) FROM existing_days) THEN 1
        WHEN date != 0 THEN 2
        ELSE 3
    END,
    -- 原表数据按日期升序排列
    CASE WHEN date != 0 THEN STR_TO_DATE(date, '%d-%m-%Y') ELSE NULL END,
    week_day;

PostgreSQL版本示例 #

PostgreSQL生成连续数字更简洁，同时注意date字段如果是字符串类型，补全的0要转为字符串：

WITH all_week_days AS (
    -- 直接生成1-7的星期数
    SELECT generate_series(1,7) AS week_day
),
existing_days AS (
    SELECT DISTINCT week_day
    FROM your_table
    WHERE TO_DATE(date, 'DD-MM-YYYY') BETWEEN '2018-02-01' AND '2018-02-28'
),
missing_days AS (
    SELECT '0'::TEXT AS date, awd.week_day
    FROM all_week_days awd
    LEFT JOIN existing_days ed ON awd.week_day = ed.week_day
    WHERE ed.week_day IS NULL
)
SELECT date, week_day
FROM (
    SELECT date, week_day
    FROM your_table
    WHERE TO_DATE(date, 'DD-MM-YYYY') BETWEEN '2018-02-01' AND '2018-02-28'
    UNION ALL
    SELECT date, week_day
    FROM missing_days
) combined
ORDER BY 
    CASE 
        WHEN date = '0' AND week_day < (SELECT MIN(week_day) FROM existing_days) THEN 1
        WHEN date != '0' THEN 2
        ELSE 3
    END,
    CASE WHEN date != '0' THEN TO_DATE(date, 'DD-MM-YYYY') ELSE NULL END,
    week_day;

方案2：不支持CTE的老版本数据库（如MySQL 5.x） #

用子查询替代CTE，实现同样的效果：

-- 先生成缺失的补全记录，再和原表数据合并
SELECT 0 AS date, week_day
FROM (
    SELECT 1 AS week_day UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
) all_days
WHERE week_day NOT IN (
    SELECT DISTINCT week_day
    FROM your_table
    WHERE STR_TO_DATE(date, '%d-%m-%Y') BETWEEN '2018-02-01' AND '2018-02-28'
)
UNION ALL
-- 原表2月的有效数据
SELECT date, week_day
FROM your_table
WHERE STR_TO_DATE(date, '%d-%m-%Y') BETWEEN '2018-02-01' AND '2018-02-28'
-- 按要求排序
ORDER BY 
    CASE 
        WHEN date = 0 AND week_day < (SELECT MIN(week_day) FROM your_table WHERE STR_TO_DATE(date, '%d-%m-%Y') BETWEEN '2018-02-01' AND '2018-02-28') THEN 1
        WHEN date != 0 THEN 2
        ELSE 3
    END,
    CASE WHEN date != 0 THEN STR_TO_DATE(date, '%d-%m-%Y') ELSE NULL END,
    week_day;

注意事项 #

1. 请将代码中的your_table替换为你的实际表名；
2. 日期转换函数请根据数据库调整：
   • SQL Server用CONVERT(DATE, date, 105)（对应DD-MM-YYYY格式）；
   • Oracle用TO_DATE(date, 'DD-MM-YYYY')；
3. 如果date字段是日期类型而非字符串，补全的0需要调整为合适的占位值（比如NULL，但根据你的需求是0，所以如果是日期类型可能需要转为字符串处理）。

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