如何提取同一ip_number下不同reservation_id的唯一预订记录？

筛选满足以下条件的记录：

• 同一ip_number在同一quote_date下对应多个不同的reservation_id
• 每个reservation_id仅保留一条记录
• 记录需匹配name、quote_date、arrival_date等字段的关联关系

DECLARE @Reservations TABLE (ip_number INT, reservation_id VARCHAR(16), name VARCHAR(16),quote_date DATETIME, arrival_date DATETIME, deposit_amount DECIMAL(16,2))

INSERT INTO @Reservations (ip_number, reservation_id, name, quote_date, arrival_date, deposit_amount)
VALUES 
  (50053177,21132003,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','288.90'),
  (50053177,21132003,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','288.90'),
  (50053177,21132003,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','288.90'),
  (50053177,21132003,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','288.90'),
  (50053177,21132003,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','288.90'),
  (50053177,21132003,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','288.90'),
  (50053177,21132518,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','64.20'),
  (50053177,21132518,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','64.20'),
  (50053177,21132714,'Christine','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','32.10'),
  (50053161,21131464,'Amy','2022-12-27 00:00:00.000','2022-12-28 07:00:00.000','52.31'),
  (50053151,21131445,'Chung','2022-12-27 00:00:00.000','2022-12-28 07:00:00.000','119.04'),
  (50053151,21131445,'Chung','2022-12-27 00:00:00.000','2022-12-28 07:00:00.000','119.04'),
  (50053151,21131445,'Chung','2022-12-27 00:00:00.000','2022-12-28 07:00:00.000','119.04'),
  (50039951,21125684,'Jennifer','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','103.88'),
  (50039951,21125683,'Jennifer','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','103.88'),
  (50039951,21125683,'Jennifer','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','103.88'),
  (50039951,21125682,'Jennifer','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','103.88'),
  (50039951,21125682,'Jennifer','2022-12-27 00:00:00.000','2022-12-29 07:00:00.000','103.88');

尝试的SQL语句 #

SELECT ROW_NUMBER() OVER (ORDER BY rth.ip_number DESC) AS row, 
       reservation_id,
       name,
       ip_number,
       quote_date,
       arrival_date,
       deposit_amount
FROM r_order_reservation
WHERE quote_date > '2022-12-01';

存在的问题 #

返回了同一reservation_id的重复记录，既没实现每个reservation_id仅保留一条的要求，也没有筛选出同一ip_number+quote_date下存在多个reservation_id的目标记录。

WITH ValidGroups AS (
    -- 筛选出同一ip_number+quote_date下存在多个不同reservation_id的分组
    SELECT ip_number, quote_date
    FROM @Reservations
    GROUP BY ip_number, quote_date
    HAVING COUNT(DISTINCT reservation_id) > 1
),
UniqueReservations AS (
    -- 对每个reservation_id仅保留一条记录，关联符合条件的分组
    SELECT DISTINCT 
           r.ip_number, 
           r.reservation_id, 
           r.name, 
           r.quote_date, 
           r.arrival_date, 
           r.deposit_amount
    FROM @Reservations r
    JOIN ValidGroups vg ON r.ip_number = vg.ip_number AND r.quote_date = vg.quote_date
)
-- 生成行号并输出最终结果
SELECT ROW_NUMBER() OVER (ORDER BY ip_number DESC, reservation_id DESC) AS row,
       reservation_id,
       name,
       ip_number,
       quote_date,
       arrival_date,
       deposit_amount
FROM UniqueReservations
WHERE quote_date > '2022-12-01';

row reservation_id  name        quote_date              arrival_date             deposit_amount
1801    21132003    Christine   2022-12-27 00:00:00.000 2022-12-29 00:00:00.000  288.90
1802    21132518    Christine   2022-12-27 00:00:00.000 2022-12-29 00:00:00.000  64.20
1803    21132714    Christine   2022-12-27 00:00:00.000 2022-12-29 00:00:00.000  32.10
1804    21125684    Jennifer    2022-12-27 00:00:00.000 2022-12-31 07:00:00.000  103.88
1815    21125683    Jennifer    2022-12-27 00:00:00.000 2022-12-30 07:00:00.000  103.88
1817    21125682    Jennifer    2022-12-27 00:00:00.000 2022-12-29 07:00:00.000  103.88

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