sql - 从查询中过滤记录

Question

我需要从以下查询中过滤数据我需要包含 maxid、minid、checkin、checkout 和其他列的记录

1. 一条记录不重复
2. 返回的记录包括一组 maxid 和 minid

3. 每个唯一的房间 ID 和预订 ID 对都应包括在内

   WITH cte AS (SELECT        Reservation_Id, checkInDate, MIN(Id) AS MinId
                        FROM            dbo.Reservation_Details
                        GROUP BY Reservation_Id, roomNoid, checkInDate), ct AS
   (SELECT        Reservation_Id, roomNoid, statusid, Id, Trans_Date, adultNo, childNo, infantNo, BoardType_Id, Userid, checkOutDate, MAX(Id) AS MaxId
   FROM            dbo.Reservation_Details AS Reservation_Details_1
   GROUP BY Reservation_Id, roomNoid, checkOutDate, statusid, Id, Trans_Date, adultNo, childNo, infantNo, BoardType_Id, Userid)
   
   
   SELECT        C.Reservation_Id, C.checkInDate, C.MinId, CT.Reservation_Id AS CTReservation_Id, CT.roomNoid, CT.statusid, CT.Id, CT.Trans_Date, CT.adultNo, CT.childNo, 
                         CT.infantNo, CT.BoardType_Id, CT.Userid, CT.checkOutDate, CT.MaxId
   FROM            cte AS C INNER JOIN
                         ct AS CT ON C.Reservation_Id = CT.Reservation_Id
   

一直想抓住我的尾巴，伙计们

下面是数据

SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO

SET IDENTITY_INSERT RoomsMS.dbo.Reservation_Details ON
GO
INSERT RoomsMS.dbo.Reservation_Details(Id, Reservation_Id, roomNoid, Trans_Date, statusid, adultNo, childNo, infantNo, BoardType_Id, checkInDate, checkOutDate, Userid) VALUES (1, 1, 37, '2019-02-08 00:00:00.0000000', 1, 1, 0, 0, 2, '2019-08-01', '2019-08-05', 1)
INSERT RoomsMS.dbo.Reservation_Details(Id, Reservation_Id, roomNoid, Trans_Date, statusid, adultNo, childNo, infantNo, BoardType_Id, checkInDate, checkOutDate, Userid) VALUES (3, 2, 40, '2019-02-08 00:00:00.0000000', 1, 1, 0, 0, 4, '2019-08-03', '2019-08-03', 1)
INSERT RoomsMS.dbo.Reservation_Details(Id, Reservation_Id, roomNoid, Trans_Date, statusid, adultNo, childNo, infantNo, BoardType_Id, checkInDate, checkOutDate, Userid) VALUES (4, 3, 39, '2019-02-12 00:00:00.0000000', 1, 2, 0, 0, 2, '2019-07-01', '2020-01-01', 2)
INSERT RoomsMS.dbo.Reservation_Details(Id, Reservation_Id, roomNoid, Trans_Date, statusid, adultNo, childNo, infantNo, BoardType_Id, checkInDate, checkOutDate, Userid) VALUES (24, 1, 40, '2019-08-04 20:29:52.0000000', 1, 1, 0, 0, 3, '2019-08-04', '2019-08-04', 1)
INSERT RoomsMS.dbo.Reservation_Details(Id, Reservation_Id, roomNoid, Trans_Date, statusid, adultNo, childNo, infantNo, BoardType_Id, checkInDate, checkOutDate, Userid) VALUES (27, 1, 40, '2019-08-04 20:58:50.0000000', 1, 1, 0, 0, 3, '2019-08-04', '2019-08-24', 1)
INSERT RoomsMS.dbo.Reservation_Details(Id, Reservation_Id, roomNoid, Trans_Date, statusid, adultNo, childNo, infantNo, BoardType_Id, checkInDate, checkOutDate, Userid) VALUES (38, 9, 45, '2019-08-15 16:37:18.0000000', 1, 3, 2, 2, 3, '2019-08-10', '2019-08-16', 1)
INSERT RoomsMS.dbo.Reservation_Details(Id, Reservation_Id, roomNoid, Trans_Date, statusid, adultNo, childNo, infantNo, BoardType_Id, checkInDate, checkOutDate, Userid) VALUES (39, 10, 45, '2019-08-15 17:46:50.0000000', 1, 2, 0, 0, 3, '2019-08-15', '2019-08-19', 1)
INSERT RoomsMS.dbo.Reservation_Details(Id, Reservation_Id, roomNoid, Trans_Date, statusid, adultNo, childNo, infantNo, BoardType_Id, checkInDate, checkOutDate, Userid) VALUES (40, 9, 46, '2019-08-15 18:15:12.0000000', 1, 1, 2, 0, 4, '2019-08-15', '2019-08-25', 1)
INSERT RoomsMS.dbo.Reservation_Details(Id, Reservation_Id, roomNoid, Trans_Date, statusid, adultNo, childNo, infantNo, BoardType_Id, checkInDate, checkOutDate, Userid) VALUES (41, 10, 48, '2019-08-15 18:34:12.0000000', 1, 1, 0, 0, 3, '2019-08-10', '2019-08-23', 1)
INSERT RoomsMS.dbo.Reservation_Details(Id, Reservation_Id, roomNoid, Trans_Date, statusid, adultNo, childNo, infantNo, BoardType_Id, checkInDate, checkOutDate, Userid) VALUES (42, 10, 38, '2019-08-15 18:34:12.0000000', 1, 1, 0, 0, 2, '2019-08-15', '2019-08-23', 1)
INSERT RoomsMS.dbo.Reservation_Details(Id, Reservation_Id, roomNoid, Trans_Date, statusid, adultNo, childNo, infantNo, BoardType_Id, checkInDate, checkOutDate, Userid) VALUES (43, 10, 40, '2019-08-15 18:34:12.0000000', 1, 1, 0, 0, 2, '2019-08-15', '2019-08-23', 1)
INSERT RoomsMS.dbo.Reservation_Details(Id, Reservation_Id, roomNoid, Trans_Date, statusid, adultNo, childNo, infantNo, BoardType_Id, checkInDate, checkOutDate, Userid) VALUES (44, 9, 40, '2019-08-16 10:17:34.0000000', 1, 2, 0, 0, 4, '2019-08-10', '2019-08-31', 1)
INSERT RoomsMS.dbo.Reservation_Details(Id, Reservation_Id, roomNoid, Trans_Date, statusid, adultNo, childNo, infantNo, BoardType_Id, checkInDate, checkOutDate, Userid) VALUES (45, 9, 45, '2019-08-16 11:24:44.0000000', 1, 3, 2, 2, 3, '2019-08-10', '2019-08-20', 1)
INSERT RoomsMS.dbo.Reservation_Details(Id, Reservation_Id, roomNoid, Trans_Date, statusid, adultNo, childNo, infantNo, BoardType_Id, checkInDate, checkOutDate, Userid) VALUES (46, 9, 50, '2019-08-17 10:51:15.0000000', 1, 2, 0, 0, 5, '2019-08-10', '2019-08-31', 1)
INSERT RoomsMS.dbo.Reservation_Details(Id, Reservation_Id, roomNoid, Trans_Date, statusid, adultNo, childNo, infantNo, BoardType_Id, checkInDate, checkOutDate, Userid) VALUES (47, 9, 51, '2019-08-17 11:25:01.0000000', 1, 2, 0, 0, 2, '2019-08-10', '2019-08-31', 1)
INSERT RoomsMS.dbo.Reservation_Details(Id, Reservation_Id, roomNoid, Trans_Date, statusid, adultNo, childNo, infantNo, BoardType_Id, checkInDate, checkOutDate, Userid) VALUES (48, 9, 38, '2019-08-17 11:25:01.0000000', 1, 2, 0, 0, 5, '2019-08-10', '2019-08-31', 1)
INSERT RoomsMS.dbo.Reservation_Details(Id, Reservation_Id, roomNoid, Trans_Date, statusid, adultNo, childNo, infantNo, BoardType_Id, checkInDate, checkOutDate, Userid) VALUES (49, 9, 41, '2019-08-17 11:19:01.0000000', 1, 2, 0, 0, 3, '2019-08-10', '2019-08-31', 1)
INSERT RoomsMS.dbo.Reservation_Details(Id, Reservation_Id, roomNoid, Trans_Date, statusid, adultNo, childNo, infantNo, BoardType_Id, checkInDate, checkOutDate, Userid) VALUES (50, 9, 67, '2019-08-17 13:14:06.0000000', 1, 2, 0, 0, 2, '2019-08-10', '2019-08-31', 1)
INSERT RoomsMS.dbo.Reservation_Details(Id, Reservation_Id, roomNoid, Trans_Date, statusid, adultNo, childNo, infantNo, BoardType_Id, checkInDate, checkOutDate, Userid) VALUES (51, 9, 42, '2019-08-17 20:07:34.0000000', 1, 1, 0, 0, 5, '2019-08-17', '2019-08-18', 1)
GO
SET IDENTITY_INSERT RoomsMS.dbo.Reservation_Details OFF
GO

标签： sqlsql-servertsql

Answer 1

WITH 
  cte AS 
  (SELECT Reservation_Id,roomNoid, checkInDate, MIN(Id) AS MinId 
      FROM dbo.Reservation_Details 
      GROUP BY Reservation_Id, roomNoid, checkInDate),
  ct AS
  (SELECT Reservation_Id, roomNoid, statusid, Id, Trans_Date, adultNo, childNo, infantNo, BoardType_Id, Userid, checkOutDate, MAX(Id) AS MaxId
      FROM dbo.Reservation_Details AS Reservation_Details_1
      GROUP BY Reservation_Id, roomNoid, checkOutDate, statusid, Id, Trans_Date, adultNo, childNo, infantNo, BoardType_Id, Userid)

SELECT * FROM (SELECT C.Reservation_Id, C.checkInDate, C.MinId, CT.Reservation_Id AS CTReservation_Id, CT.roomNoid, CT.statusid, CT.Id, CT.Trans_Date, CT.adultNo, CT.childNo, CT.infantNo, CT.BoardType_Id, CT.Userid, CT.checkOutDate, CT.MaxId
FROM cte AS C 
INNER JOIN ct AS CT ON C.Reservation_Id = CT.Reservation_Id AND  C.roomNoid = CT.roomNoid)R1
LEFT OUTER JOIN dbo.Reservation_Details R2
  ON R1.Reservation_Id = R2.Reservation_Id AND  r1.roomNoid = r2.roomNoid
    AND (R1.Reservation_Id > R2.Reservation_Id
      OR R1.Reservation_Id = R2.Reservation_Id
      AND R1.id < R2.id)
WHERE R2.Id IS NULL
ORDER BY R1.Reservation_Id, R1.roomNoid