帮助查询只返回最近的记录

Question

我正在尝试创建一个简单的视图，从两个数据库获取数据。

我有一个 booking 表，仅显示每个预订一次，还有一个日记表，用于记录每个预订。

每个预订都会在日记表中多次显示，但我只希望在我的视图中看到最新的注释和预订信息

出于某种原因，这不起作用

SELECT     
    t0.BOOKNO, t0.PARTY, t0.TOUR, t0.DEPART, t0.DEP7, t0.BOOK7, t0.PAID, t0.APRICE, t1.TEXT
FROM         
    (SELECT DISTINCT 
        TOP (100) PERCENT 
        dbo.DIARY.ADDED, dbo.BOOKINGS.BOOKNO, dbo.BOOKINGS.PARTY, 
        dbo.BOOKINGS.TOUR, dbo.BOOKINGS.DEPART, 
        dbo.BOOKINGS.DEP7, dbo.BOOKINGS.BOOKED, dbo.BOOKINGS.BOOK7, dbo.BOOKINGS.PAID, 
        dbo.BOOKINGS.APRICE, dbo.BOOKINGS.STATUS
     FROM          
        dbo.BOOKINGS 
     CROSS JOIN
        dbo.DIARY
     WHERE      
        (dbo.BOOKINGS.PAID IS NULL) 
        OR (dbo.BOOKINGS.PAID = 0)
     GROUP BY 
        dbo.BOOKINGS.BOOKNO, dbo.BOOKINGS.PARTY, dbo.BOOKINGS.TOUR, dbo.BOOKINGS.DEPART, 
        dbo.BOOKINGS.DEP7, dbo.BOOKINGS.BOOKED, dbo.BOOKINGS.APRICE, dbo.BOOKINGS.STATUS, 
        dbo.BOOKINGS.BOOK7, dbo.BOOKINGS.PAID, dbo.DIARY.ADDED
     HAVING      
        (dbo.BOOKINGS.STATUS <> 'xl') AND 
        (dbo.BOOKINGS.DEPART > CONVERT(int, GETDATE()))
     ORDER BY 
        dbo.BOOKINGS.BOOKNO) AS t0 
INNER JOIN
   (SELECT     
       BOOKNO, TEXT, MAX(ADDED) AS added
    FROM          
       dbo.DIARY AS DIARY_1
    GROUP BY BOOKNO, TEXT) AS t1 ON t0.BOOKNO = t1.BOOKNO

我尝试了此操作，但它不起作用无法解析查询文本？

;WITH MostRecentBookings AS (  SELECT     dbo.DIARY.ADDED7, dbo.DIARY.ADDED, dbo.BOOKINGS.BOOKNO, dbo.BOOKINGS.PARTY, dbo.BOOKINGS.TOUR, dbo.BOOKINGS.DEPART, 
                      dbo.BOOKINGS.DEP7, dbo.BOOKINGS.BOOKED, dbo.BOOKINGS.BOOK7, dbo.BOOKINGS.PAID, dbo.BOOKINGS.APRICE, dbo.BOOKINGS.STATUS, 
                      dbo.DIARY.TEXT, ROW_NUMBER() OVER(PARTITION BY dbo.BOOKINGS.BOOKNO ORDER BY dbo.DIARY.ADDED DESC) AS 'RowNum'  
FROM         dbo.BOOKINGS INNER JOIN   dbo.DIARY ON dbo.BOOKINGS.BOOKNO = dbo.DIARY.BOOKNO 
WHERE     (dbo.BOOKINGS.PAID IS NULL) OR     (dbo.BOOKINGS.PAID = 0)) 
SELECT    *  FROM    MostRecentBookings 
WHERE    RowNum = 1 

Answer 1

基本上，您可以使用 CTE（通用表表达式）与 ROW_NUMBER() 函数相结合来实现此目的。类似于：

;WITH MostRecentBookings AS
(
   SELECT 
      (some columns),
      ROW_NUMBER() OVER(PARTITION BY b.BOOK_NO ORDER BY d.ADDED DESC) AS 'RowNum'
   FROM
      dbo.Booking b
   INNER JOIN
      dbo.Diary d ON ..........
)
SELECT
   (some columns)
FROM
   MostRecentBookings
WHERE
   RowNum = 1

基本上，CTE 会在两个表之间进行连接，然后按 BOOK_NO 对数据进行“分区” - 这样，每个唯一的预订都会获得一个从 1 开始的行号。

编号方式是最近的条目编号为 1，前一个条目编号为后续更高的编号。从该 CTE 中，选择 RowNum = 1 的条目作为最近预订的列表。