mysql - 结合两个查询sql

Question

我不知道如何组合以下查询

查询 1

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 

ORDER by hotel.country, time.year, time.month

查询 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

我期望输出如下

国家、年、月、预订次数、结帐次数、总和(预订+结帐)

有什么建议吗？

Answer 1

将 mysql Union 与聚合结合使用。

工会文件:https://dev.mysql.com/doc/refman/5.7/en/union.html聚合文档:https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html

联合示例如下:

SELECT 
    firstName, 
    lastName
FROM
    employees 
UNION 
SELECT 
    contactFirstName, 
    contactLastName
FROM
    customers;

希望这对您有所帮助!