合并week1与week2查询后专属记录丢失的SQL问题求助

问题根源 #

你用LEFT JOIN合并两个查询，但WHERE子句里的week1.BERTH_YEAR = 2024和week2.BERTH_YEAR = 2024直接把只在单周存在的船只过滤掉了——毕竟只在week1有记录的船，week2的字段全是NULL，week2.BERTH_YEAR = 2024这个条件会把它排除；反过来只在week2有记录的船，也会被week1.BERTH_YEAR = 2024踢出去，最后只剩同时在两周都有记录的78条。

解决方案1：把年份条件移到JOIN的ON子句里 #

把年份判断放到LEFT JOIN的关联条件中，而不是WHERE里，这样就不会过滤掉左连接生成的NULL记录：

SELECT 
    BOAT.BOAT_NAME, week1.BERTH_WEEK, week2.BERTH_WEEK 
FROM 
    BOAT
LEFT JOIN 
    week1 ON BOAT.BOAT_ID = week1.BOAT AND week1.BERTH_YEAR = 2024
LEFT JOIN 
    week2 ON BOAT.BOAT_ID = week2.BOAT AND week2.BERTH_YEAR = 2024
ORDER BY 
    BOAT.BOAT_NAME;

这样查询会返回所有在week1、week2，或者两周都有记录的船只，没有数据的周对应字段会显示NULL。

解决方案2：用UNION ALL分条展示（适合不同报表需求） #

如果报表需要把两周的记录分开显示，而不是同一行展示两周数据，可以用UNION ALL合并：

SELECT 
    BOAT.BOAT_NAME, week1.BERTH_WEEK, NULL AS BERTH_WEEK_W2
FROM 
    BOAT
JOIN 
    week1 ON BOAT.BOAT_ID = week1.BOAT
WHERE 
    week1.BERTH_YEAR = 2024

UNION ALL

SELECT 
    BOAT.BOAT_NAME, NULL AS BERTH_WEEK_W1, week2.BERTH_WEEK
FROM 
    BOAT
JOIN 
    week2 ON BOAT.BOAT_ID = week2.BOAT
WHERE 
    week2.BERTH_YEAR = 2024

ORDER BY 
    BOAT.BOAT_NAME;

这种方式会把week1和week2的记录分别列出，对应周没有数据的字段显示NULL。

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