如何合并两个SQL查询？附待合并的查询语句

嘿，我来帮你搞定这两个SQL的合并问题！首先我先把你没写完的QUERY 2补全（逻辑和QUERY 1保持一致），然后给你几种靠谱的合并方式：

先补全完整的QUERY 2 #

SELECT hotel.country, time.year, time.month, COUNT(checkout.room_id) as checkedout 
FROM checkout 
LEFT JOIN room on room.room_id = checkout.room_id 
LEFT JOIN hotel on room.hotel_id = hotel.hotel_id 
LEFT JOIN time on checkout.time_id = time.time_id 
GROUP BY hotel.country, time.year, time.month 
ORDER by hotel.country, time.year, time.month

方法1：用CTE（公共表表达式）合并（推荐，可读性高） #

这种方式把两个查询的结果分别存为临时数据集，再通过FULL OUTER JOIN关联，确保即使某个时间段只有预订或只有退房记录也能显示出来，用COALESCE把空值替换成0，避免数据缺失：

WITH booked_data AS (
    SELECT hotel.country, time.year, time.month, COUNT(booking.room_id) as booked 
    FROM booking 
    LEFT JOIN room on room.room_id = booking.room_id 
    LEFT JOIN hotel on room.hotel_id = hotel.hotel_id 
    LEFT JOIN time on booking.time_id = time.time_id 
    GROUP BY hotel.country, time.year, time.month
),
checkedout_data AS (
    SELECT hotel.country, time.year, time.month, COUNT(checkout.room_id) as checkedout 
    FROM checkout 
    LEFT JOIN room on room.room_id = checkout.room_id 
    LEFT JOIN hotel on room.hotel_id = hotel.hotel_id 
    LEFT JOIN time on checkout.time_id = time.time_id 
    GROUP BY hotel.country, time.year, time.month
)
SELECT 
    COALESCE(b.country, c.country) as country,
    COALESCE(b.year, c.year) as year,
    COALESCE(b.month, c.month) as month,
    COALESCE(b.booked, 0) as booked,
    COALESCE(c.checkedout, 0) as checkedout
FROM booked_data b
FULL OUTER JOIN checkedout_data c
    ON b.country = c.country 
    AND b.year = c.year 
    AND b.month = c.month
ORDER BY country, year, month;

方法2：用子查询直接关联 #

如果你的数据库版本不支持CTE（比如老版本的MySQL），可以直接把两个查询作为子表来关联，逻辑和上面一致：

SELECT 
    COALESCE(b.country, c.country) as country,
    COALESCE(b.year, c.year) as year,
    COALESCE(b.month, c.month) as month,
    COALESCE(b.booked, 0) as booked,
    COALESCE(c.checkedout, 0) as checkedout
FROM (
    SELECT hotel.country, time.year, time.month, COUNT(booking.room_id) as booked 
    FROM booking 
    LEFT JOIN room on room.room_id = booking.room_id 
    LEFT JOIN hotel on room.hotel_id = hotel.hotel_id 
    LEFT JOIN time on booking.time_id = time.time_id 
    GROUP BY hotel.country, time.year, time.month
) b
FULL OUTER JOIN (
    SELECT hotel.country, time.year, time.month, COUNT(checkout.room_id) as checkedout 
    FROM checkout 
    LEFT JOIN room on room.room_id = checkout.room_id 
    LEFT JOIN hotel on room.hotel_id = hotel.hotel_id 
    LEFT JOIN time on checkout.time_id = time.time_id 
    GROUP BY hotel.country, time.year, time.month
) c
    ON b.country = c.country 
    AND b.year = c.year 
    AND b.month = c.month
ORDER BY country, year, month;

方法3：兼容不支持FULL OUTER JOIN的数据库（比如MySQL） #

有些数据库（比如MySQL）不支持FULL OUTER JOIN，可以用LEFT JOIN + UNION的方式模拟，确保所有时间段都被覆盖：

WITH booked_data AS (
    SELECT hotel.country, time.year, time.month, COUNT(booking.room_id) as booked 
    FROM booking 
    LEFT JOIN room on room.room_id = booking.room_id 
    LEFT JOIN hotel on room.hotel_id = hotel.hotel_id 
    LEFT JOIN time on booking.time_id = time.time_id 
    GROUP BY hotel.country, time.year, time.month
),
checkedout_data AS (
    SELECT hotel.country, time.year, time.month, COUNT(checkout.room_id) as checkedout 
    FROM checkout 
    LEFT JOIN room on room.room_id = checkout.room_id 
    LEFT JOIN hotel on room.hotel_id = hotel.hotel_id 
    LEFT JOIN time on checkout.time_id = time.time_id 
    GROUP BY hotel.country, time.year, time.month
)
SELECT 
    b.country,
    b.year,
    b.month,
    b.booked,
    COALESCE(c.checkedout, 0) as checkedout
FROM booked_data b
LEFT JOIN checkedout_data c
    ON b.country = c.country 
    AND b.year = c.year 
    AND b.month = c.month
UNION
SELECT 
    c.country,
    c.year,
    c.month,
    COALESCE(b.booked, 0) as booked,
    c.checkedout
FROM checkedout_data c
LEFT JOIN booked_data b
    ON c.country = b.country 
    AND c.year = b.year 
    AND c.month = b.month
WHERE b.country IS NULL
ORDER BY country, year, month;

小提醒 #

要确认booking.time_id和checkout.time_id对应的时间维度是否符合你的业务需求——比如你是要统计当月预订的房间数和当月退房的房间数，那当前的关联逻辑是正确的；如果有特殊的时间匹配规则，需要调整JOIN条件哦。

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