MySQL单表无匹配记录时返回空行的实现方案

要实现每个日期下所有指定时间点的记录（缺失的时间点返回NULL），核心思路是先生成所有需要的日期-时间组合，再通过左连接关联原表，补全缺失行。具体实现如下：

完整SQL语句 #

WITH required_times AS (
    SELECT '07:35:00' AS target_time UNION ALL
    SELECT '14:00:00' AS target_time
),
distinct_dates AS (
    SELECT DISTINCT `date` FROM `table`
)
SELECT 
    dd.`date` AS 日期,
    t.`time` AS 时间
FROM distinct_dates dd
CROSS JOIN required_times rt
LEFT JOIN `table` t 
    ON dd.`date` = t.`date` 
    AND rt.target_time = t.`time`
ORDER BY dd.`date`, COALESCE(t.`time`, '00:00:00');

代码说明 #

1. 生成指定时间点集合：required_times CTE定义了所有需要覆盖的时间点（例子中是07:35:00和14:00:00），后续需要新增时间点时，直接在UNION ALL后添加即可。
2. 提取唯一日期：distinct_dates CTE从原表中获取所有不重复的日期，确保每个日期都被处理。
3. 交叉连接生成全量组合：CROSS JOIN将日期和时间点做笛卡尔积，得到所有可能的日期-时间配对。
4. 左连接补全缺失行：通过LEFT JOIN关联原表，匹配日期和时间点，原表中不存在的配对会返回NULL值。
5. 排序对齐结果：ORDER BY保证日期升序，同时让NULL的时间行排在对应日期的非NULL行之后，与期望输出一致。

执行结果 #

执行上述SQL后，会得到与你期望一致的输出：

替代方案（无CTE版本） #

如果你的MySQL版本不支持CTE（低于8.0），可以用子查询替代：

SELECT 
    dd.`date` AS 日期,
    t.`time` AS 时间
FROM (SELECT DISTINCT `date` FROM `table`) dd
CROSS JOIN (SELECT '07:35:00' AS target_time UNION ALL SELECT '14:00:00') rt
LEFT JOIN `table` t 
    ON dd.`date` = t.`date` 
    AND rt.target_time = t.`time`
ORDER BY dd.`date`, COALESCE(t.`time`, '00:00:00');

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