如何在Postgresql的不同城市获得最新约会

Question

我目前正在尝试立即在PostgreSQL上执行一项任务，在那里我需要在最后预订日期准备一个城市清单，其中还必须包括酒店ID和照片。

这是迄今为止我的SQL查询：

SELECT city.name, booking.booking_date as last_booking_date, hotel.id as hotel_id, hotel.photos ->> 0 as hotel_photo
FROM city 
INNER JOIN hotel
    ON city.id = hotel.city_id 
INNER JOIN booking 
    ON booking.hotel_id = hotel.id
WHERE 
    booking.booking_date = (SELECT MAX(booking_date) FROM booking WHERE hotel_id = hotel.id)
ORDER BY city.name ASC;

现在所做的就是返回城市（按字母顺序排序，可以复制），其中还包含其预订日期（也从最早到最新情况）。如果许多酒店都有相同数量的预订，那么请多亏下面的答案，我必须通过ID进行排序

，并且有一些小调整，我能够按字母顺序排列不同的城市，但在他们的最新日期之前却不能返回不同的城市对于某些城市。

SELECT distinct on (city.name) 
  city.name, 
  booking.booking_date as last_booking_date, 
  hotel.id as hotel_id, 
  hotel.photos ->> 0 as hotel_photo
FROM city 
INNER JOIN hotel
    ON city.id = hotel.city_id 
INNER JOIN booking 
    ON booking.hotel_id = hotel.id
WHERE 
    booking.booking_date = (SELECT MAX(booking_date) 
                            FROM booking
                            WHERE hotel_id = hotel.id)
    AND 
    booking.id = (SELECT MAX(id) FROM booking WHERE hotel_id = hotel.id)
ORDER BY city.name, hotel.id ASC, booking.booking_date ASC;

这具有以下结果，还显示了预期的结果。

如您所见，有些城市没有按其最新预订日期进行排序。我尝试了上面的SQL查询的变体：

SELECT distinct on (city.name) 
  city.name, 
  booking.booking_date as last_booking_date, 
  hotel.id as hotel_id, 
  hotel.photos ->> 0 as hotel_photo
FROM city 
INNER JOIN hotel
    ON city.id = hotel.city_id 
INNER JOIN booking 
    ON booking.hotel_id = hotel.id
WHERE 
    booking.booking_date = (SELECT MAX(booking_date) 
                            FROM booking
                            WHERE hotel_id = hotel.id)
    AND 
    booking.hotel_id = (SELECT MAX(hotel_id) FROM booking WHERE hotel_id = hotel.id)
ORDER BY city.name, hotel.id ASC, booking.booking_date ASC;

但是

SELECT distinct on (city.name) 
  city.name, 
  booking.booking_date as last_booking_date, 
  hotel.id as hotel_id, 
  hotel.photos ->> 0 as hotel_photo
FROM city 
INNER JOIN hotel
    ON city.id = hotel.city_id 
INNER JOIN booking 
    ON booking.hotel_id = hotel.id
WHERE 
    booking.booking_date = (SELECT MAX(booking_date) 
                            FROM booking
                            WHERE hotel_id = hotel.id)
    AND 
    booking.id = (SELECT MAX(id) FROM booking WHERE hotel_id = hotel.id)
    AND 
    booking.hotel_id = (SELECT MAX(hotel_id) FROM booking WHERE hotel_id = hotel.id)
ORDER BY city.name, hotel.id ASC, booking.booking_date ASC;

它们都显示出与之前的结果相同的结果。

这样，如何与最新日期获得行？对于上下文，这是第一个查询返回的内容。

Answer 1

前几天，我看到了一个类似的问题，类似这样的问题

写了一个查询来检索城市名称，该城市最新预订的日期是最受欢迎的酒店的ID（这是最多的酒店的酒店预订），以及每个城市的那家酒店的照片。结果应按城市名称按升序排序。如果两家酒店的预订数量相同，则应按酒店ID进行排序。

问题声明

数据库架构

CREATE TABLE city
(
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE hotel
(
    id INT PRIMARY KEY,
    city_id INT NOT NULL REFERENCES cityl,
    name VARCHAR(50) NOT NULL,
    day_price NUMERIC(8, 2) NOT NULL,
    photos JSONB DEFAULT '[]'
);


CREATE TABLE booking
(
    id int PRIMARY KEY,
    hotel_id INT NOT NULL REFERENCES hotel,
    booking_date DATE NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL
);

描述

您的任务是准备一个城市中最后预订日期的城市清单这座城市最受欢迎的（按预订数量）酒店的主要照片（照片[0]）。

按城市按顺序排序的结果。如果许多酒店的预订数量相同，则按ID（上升顺序）对其进行排序。
请记住，查询也将运行不同的数据集。

示例输出

示例数据

表：城市

表：酒店

表：预订

预期输出

解决方案

with bookingCount as (
    select
        h.id hotel_id,
        h.city_id,
        h.photos ->> 0 AS hotel_photo,
        count(b.id) b_count
    from hotel h
    join booking b
    on h.id = b.hotel_id
    group by h.id
)
select
    sb1.name city_name,
    sb1.last_booking_date,
    sb2.hotel_id,
    hotel_photo
from
(
    select
        c.id city_id,
        c.name ,
        max(b.booking_date) last_booking_date
    from city c
    join hotel h
    on h.city_id = c.id
    join booking b
    on b.hotel_id = h.id
    group by c.id
) sb1
join
(
    select
        bc1.city_id,
        min(bc1.hotel_id) hotel_id,
        hotel_photo
    from
    bookingCount bc1
    where bc1.b_count = (
        SELECT MAX(b_count)
        FROM BookingCount bc2
        WHERE bc1.city_id = bc2.city_id
    )
    group by
        bc1.city_id,
        hotel_photo
) sb2
on sb2.city_id = sb1.city_id
order by
    city_name,
    hotel_id
;

Answer 2

在上使用。根据子句的顺序中的顺序，它将选择子集的第一个元素。例如：

SELECT distinct on (city.name) -- changed here
  city.name, 
  booking.booking_date as last_booking_date, 
  hotel.id as hotel_id, 
  hotel.photos ->> 0 as hotel_photo
FROM city 
INNER JOIN hotel
    ON city.id = hotel.city_id 
INNER JOIN booking 
    ON booking.hotel_id = hotel.id
WHERE 
    booking.booking_date = (SELECT MAX(booking_date) 
                            FROM booking
                            WHERE hotel_id = hotel.id)
ORDER BY city.name, booking.booking_date DESC; -- changed here

Answer 3

我相信，您不需要子查询。
您的按声明订单略有关闭：它首先按城市分类，然后是酒店，然后才选择约会。
因此，如果您修改订单按City.Name，日期desc，然后是Hotel_id，则应进行工作。
找到以下代码：

SELECT distinct on (city.name) 
  city.name, 
  booking.booking_date as last_booking_date, 
  hotel.id as hotel_id, 
  hotel.photos ->> 0 as hotel_photo
FROM city 
INNER JOIN hotel
    ON city.id = hotel.city_id 
INNER JOIN booking 
    ON booking.hotel_id = hotel.id
ORDER BY city.name, booking.booking_date DESC, hotel.id ASC;