在 mySQL 中组合两个查询 [英] Combining two queries in mySQL

问题描述

我正在尝试合并两个查询的结果.我不是很精通mysql，所以我在这里寻求帮助.

I'm trying to combine the results of two queries. I'm not very proficient in mysql so I'm here for some help.

第一个查询如下:

select count(roomtypeid) as bookedrooms, day 
from event_guest_hotel 
where hotelid = 1 and roomtypeid = 1 
 group by day;

返回:

第二个查询:

SELECT ehr.reservationid, ehr.day, h.name AS hotelname, 
ehr.totalrooms as requested_rooms, r.name AS roomname
            FROM event_hotel_reservation ehr 
            INNER JOIN hotel_room_type r 
            ON ehr.roomtypeid = r.roomtypeid 
            INNER JOIN hotel h
            ON ehr.hotelid = h.hotelid  
            WHERE totalRooms != 0 
            AND reservationID = '1'

返回:

我能否将第一个查询与第二个查询结合起来，以便在roomname"旁边的另一个结果列中获得第一个查询的结果?这样我就可以知道已经预订了多少房间，以及最初通过一个查询请求了多少房间.

Can I combine the first query with the second one, so I get the results of the first one in another resultcolumn next to 'roomname'? That way I know how many rooms are already booked and how many were originally requested from one single query.

推荐答案

尝试:

SELECT ehr.reservationid, ehr.day, h.name AS hotelname, 
    ehr.totalrooms as requested_rooms, r.name AS roomname, 
    egh.bookedrooms
FROM event_hotel_reservation ehr 
INNER JOIN hotel_room_type r ON ehr.roomtypeid = r.roomtypeid 
INNER JOIN hotel h ON ehr.hotelid = h.hotelid    
left outer join (
    select hotelid, count(roomtypeid) as bookedrooms, day 
    from event_guest_hotel 
    where roomtypeid = 1 
    group by hotelid, day
) egh on h.hotelid = egh.hotelid and ehr.day = egh.day
WHERE totalRooms != 0 
    AND reservationID = '1'

这篇关于在 mySQL 中组合两个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！