使用PostgreSQL查询预订同一房间的唯一客人及重复行

首先明确需求：我们需要从bookings表中筛选出那些被多个不同客人预订过的房间的所有相关预订记录，同时每个房间要展示对应的唯一客人列表。

先整理你的表结构与完整数据（补全了截断的1007条数据以便演示）：

方法1：用窗口函数+CTE实现（可读性强） #

先通过窗口函数标记出每个房间的不同客人数量，筛选出客人数量大于1的房间，再聚合每个房间的唯一客人列表：

WITH room_guest_counts AS (
    SELECT 
        *,
        COUNT(DISTINCT guest_id) OVER (PARTITION BY room_number) AS distinct_guest_count
    FROM bookings
),
room_unique_guests AS (
    SELECT 
        room_number,
        ARRAY_AGG(DISTINCT guest_id ORDER BY guest_id) AS unique_guests
    FROM bookings
    GROUP BY room_number
    HAVING COUNT(DISTINCT guest_id) > 1
)
SELECT 
    rc.booking_id,
    rc.check_in_date,
    rc.check_out_date,
    rc.guest_id,
    rc.room_number,
    ru.unique_guests
FROM room_guest_counts rc
JOIN room_unique_guests ru ON rc.room_number = ru.room_number
WHERE rc.distinct_guest_count > 1
ORDER BY rc.room_number, rc.booking_id;

方法2：用子查询简化实现 #

如果觉得CTE太繁琐，也可以直接用子查询关联原表和聚合结果：

SELECT 
    b.booking_id,
    b.check_in_date,
    b.check_out_date,
    b.guest_id,
    b.room_number,
    agg.unique_guests
FROM bookings b
JOIN (
    SELECT 
        room_number,
        ARRAY_AGG(DISTINCT guest_id ORDER BY guest_id) AS unique_guests
    FROM bookings
    GROUP BY room_number
    HAVING COUNT(DISTINCT guest_id) > 1
) agg ON b.room_number = agg.room_number
ORDER BY b.room_number, b.booking_id;

结果说明 #

执行以上SQL后，会返回所有被多个不同客人预订过的房间的全部预订记录，同时unique_guests列会以数组形式展示该房间对应的所有唯一客人ID。比如房间1104会返回booking_id 1002和1006的记录，unique_guests为{1,2}；房间702会返回1001和1007的记录，unique_guests为{1,3}。

如果只需要每个重复房间的唯一客人列表，不需要每条预订记录，可以用更简洁的查询：

SELECT 
    room_number,
    ARRAY_AGG(DISTINCT guest_id ORDER BY guest_id) AS unique_guests,
    COUNT(DISTINCT guest_id) AS guest_count
FROM bookings
GROUP BY room_number
HAVING COUNT(DISTINCT guest_id) > 1
ORDER BY room_number;

内容的提问来源于stack exchange，提问作者fcarlone