mysql - MySQL查询以查找每个组中重复次数最多的项目

架构为

Hotel      (hotelNo, hotelName, city)
Room      (roomNo, hotelNo, rtype, price)
Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)
Guest      (guestNo, guestName, guestAddress)

我想为布鲁金斯的每家酒店的最预订房间类型编写SQL查询？（布鲁金斯是一个城市）

我尝试了很多方法，但似乎都没有用

select rtype
from room r natural
join hotel h
where rtype = (
        select distinct (rtype)
        from room r1 natural
        join booking
        where r1.hotelno = h.hotelno
        group by rtype
        having count(*) = (
                select max(c)
                from (
                    select count() as c
                    from room r2 natural
                    join booking
                    where r2.hotelno = h.hotelno
                    group by rtype
                    ) as x
                )
        )
group by h,
    hotelno

最佳答案

我认为这是您想要的：

select *
from Room r
join hotel h on r.hotelNo = h.hotelNo
where r.rtype in (
        select r.rtype
        from Room r2
        join Booking b on r2.hotelNo = b.hotelNo
            and r2.roomNo = b.roomNo
        where r.hotelNo = r2.hotelNo
        group by r.rtype
        order by count(*) desc limit 1
        );