Блог

У меня есть эта схема:

 Hotel (**hotelNo**, hotelName, city)
Room (**roomNo, hotelNo**, type, price)
Booking (**hotelNo, guestNo, dateFrom**, dateTo, roomNo)
Guest (**guestNo**, guestName, guestAddress)
** denotes primary keys
  

Я должен выполнить этот запрос:

• Отобразите каждый отель и его наиболее распространенный номер.

У меня есть этот запрос, который не совсем корректен:

 SELECT r.hotelno, type, count(*)
FROM Hotel h, room r
WHERE h.hotelNo = r.hotelno
GROUP BY r.hotelNo, type;
  

Это то, что он выводит:

Что я делаю не так?

Похоже, что вы ищете тип номера, в котором имеется максимальное количество бронирований для номеров данного типа в каждом отеле — совокупность (максимум) другого агрегата (количество бронирований типа номера).

Создавайте его по частям. Количество бронирований номеров каждого типа в каждом отеле:

 SELECT r.hotelno, r.type, count(*) AS num_bookings
  FROM Booking AS b
  JOIN Room AS r ON b.hotelNo = r.hotelno AND b.roomNo = r.roomNo
 GROUP BY r.hotelNo, r.type;
  

Теперь вам нужно знать, какой тип номера имеет максимальное количество номеров в каждом отеле. Это должно быть сделано в два этапа:

1. Найдите максимальное количество бронирований в отеле для любого типа.
2. Найдите типы комнат с этим максимальным числом.

Первый этап заключается:

 SELECT s.hotelno, MAX(num_bookings) AS max_bookings
  FROM (SELECT r.hotelno, r.type, count(*) AS num_bookings
          FROM Booking AS b
          JOIN Room AS r ON b.hotelNo = r.hotelno AND b.roomNo = r.roomNo
         GROUP BY r.hotelNo, r.type
       ) AS s
 GROUP BY s.hotelno;
  

На втором этапе используются оба предыдущих результата для окончательного ответа:

 SELECT t.hotelno, t.type
  FROM (SELECT r.hotelno, r.type, count(*) AS num_bookings
          FROM Booking AS b
          JOIN Room AS r ON b.hotelNo = r.hotelno AND b.roomNo = r.roomNo
         GROUP BY r.hotelNo, r.type) AS t
  JOIN (SELECT s.hotelno, MAX(num_bookings) AS max_bookings
          FROM (SELECT r.hotelno, r.type, count(*) AS num_bookings
                  FROM Booking AS b
                  JOIN Room AS r ON b.hotelNo = r.hotelno AND b.roomNo = r.roomNo
                 GROUP BY r.hotelNo, r.type
               ) AS s
         GROUP BY s.hotelno) AS m
    ON t.hotelno = m.hotelno AND t.num_bookings = m.max_bookings;
  

Если ваша СУБД поддерживает предложения WITH , вы можете написать это более кратко.

Если вы ищете популярность, вам нужно будет принять во внимание таблицу бронирования. Добавьте таблицу бронирования в свой оператор FROM, ссылку на hotelNo и roomNo и выполните подсчет в таблице бронирования. Это должно дать вам нужные значения.

Редактировать: Вот несколько примеров кода для вас (протестировано):

 SELECT TOP (100) PERCENT dbo.Hotel.hotelName, dbo.Room.type, COUNT(*) AS Count
FROM dbo.Booking INNER JOIN
dbo.Room ON dbo.Booking.roomNo = dbo.Room.roomNo AND dbo.Booking.hotelNo = dbo.Room.hotelNo 
INNER JOIN dbo.Hotel ON dbo.Room.hotelNo = dbo.Hotel.hotelNo
GROUP BY dbo.Hotel.hotelName, dbo.Room.type
ORDER BY Count DESC
  

Я думаю, вам придется использовать внутренний запрос, чтобы заставить этот запрос работать:

 SELECT dbo.Hotel.hotelName, pop.type, pop.Count
FROM dbo.Hotel
    INNER JOIN (
        SELECT TOP 1 dbo.Hotel.hotelNo, dbo.Room.type, COUNT(*) AS Count
        FROM dbo.Hotel
            INNER JOIN dbo.Room ON dbo.Room.hotelNo = dbo.Hotel.hotelNo
            INNER JOIN dbo.Booking ON dbo.Booking.roomNo = dbo.Room.roomNo AND dbo.Booking.hotelNo = dbo.Hotel.hotelNo
        GROUP BY dbo.Hotel.hotelNo, dbo.Room.type
        ORDER BY Count DESC, dbo.Room.type
    ) AS pop ON pop.hotelNo = dbo.Hotel.HotelNo
ORDER BY dbo.Hotel.hotelName