MySQL 8.0：如何基于指定日期筛选完整周数据（优先指定日期行）

表结构与现有数据 #

需求规则 #

• 若指定日期存在于表中（如2024-05-29）：优先选取该日期对应的行，其余day（1-7）选取date为NULL的行，确保覆盖1-7日完整数据
• 若指定日期不存在于表中（如2024-05-05）：直接选取所有date为NULL的1-7日数据

单条SQL实现方案 #

可以通过单条SQL语句实现，核心思路是利用窗口函数ROW_NUMBER()对每个day分组，按优先级排序（指定日期的行优先级最高，其次是NULL行），然后取每个day的第一行：

WITH ranked_data AS (
    SELECT 
        id, 
        day, 
        date,
        ROW_NUMBER() OVER (
            PARTITION BY day 
            ORDER BY CASE WHEN date = '2024-05-29' THEN 0 ELSE 1 END
        ) AS rn
    FROM your_table_name
    WHERE date IS NULL OR date = '2024-05-29'
)
SELECT id, day, date
FROM ranked_data
WHERE rn = 1
ORDER BY day;

逻辑说明 #

1. CTE筛选与排序：
   • 先筛选出date为NULL或等于指定日期的行，减少数据处理量
   • 按day分组，为每个分组内的行设置优先级：指定日期的行优先级（0）高于NULL行（1）
   • 使用ROW_NUMBER()为每个分组内的行分配序号，优先级高的行序号为1
2. 主查询取数：选取每个day分组中序号为1的行，最后按day排序输出，确保结果是1-7日的完整数据

动态参数适配 #

如果需要动态传入目标日期，可使用MySQL预处理语句实现：

PREPARE stmt FROM '
WITH ranked_data AS (
    SELECT 
        id, 
        day, 
        date,
        ROW_NUMBER() OVER (
            PARTITION BY day 
            ORDER BY CASE WHEN date = ? THEN 0 ELSE 1 END
        ) AS rn
    FROM your_table_name
    WHERE date IS NULL OR date = ?
)
SELECT id, day, date
FROM ranked_data
WHERE rn = 1
ORDER BY day;
';
SET @target_date = '2024-05-29';
EXECUTE stmt USING @target_date, @target_date;
DEALLOCATE PREPARE stmt;

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