主页 / dba / 问题 / 262126
Accepted
Phil
Asked: 2020-03-18 08:56:37 +0800 CST

按辅助键过滤结果集的最佳方法

  • 772

我有一个带有主键和辅助键的表，例如

CREATE TABLE Entry (
     PrimaryKey int NOT NULL,
     SecondaryKey int NOT NULL,
     Data nvarchar(100) NOT NULL,
     DateCreated DATETIME NOT NULL
);

    INSERT INTO Entry VALUES(1,1,'test',getutcdate()),
(2,1,'test',getutcdate()),
(3,2,'test',getutcdate()),
(4,3,'test',getutcdate()),
(5,3,'test',getutcdate()),
(6,3,'test',getutcdate()),
(7,4,'test',getutcdate()),
(8,4,'test',getutcdate())

我希望能够返回按创建日期排序的条目，但每个辅助键只有 1 个条目。所以有了上面的数据，我只会得到 4 行，每个辅助键的最新条目。

最好的方法是什么？

更新：下面回答的问题，但我接下来的问题是“考虑该表是否有多个数据字段，什么是更好的查询？” 原始响应，所有内容都在 CTE 中：

with CTE as (
 select [PrimaryKey], SecondaryKey, [Data1],[Data2],[Data3],...DateCreated,
        rn = row_number() over (partition by SecondaryKey order by DateCreated desc)
  from dbo.Entry
  )
 select [PrimaryKey], SecondaryKey, [Data1],[Data2],[Data3],...,DateCreated
 From CTE where rn = 1

或者在另一个查询中，保存在 CTE 中的数据被最小化：

    with CTE as (
     select [PrimaryKey], SecondaryKey, DateCreated,
            rn = row_number() over (partition by SecondaryKey order by DateCreated desc)
      from dbo.Entry
      )
     SELECT PrimaryKey,SecondaryKey, [Data1],[Data2],[Data3],...,DateCreated 
FROM ENTRY
WHERE PrimaryKey IN( select [PrimaryKey] FROM CTE where rn = 1 )
sql-server query-performance

1 个回答

  1. Best Answer

    你应该只使用

    row_number() over (partition by SecondaryKey 
                   order by DateCreated desc)

    这将按 SecondaryKey 对数据进行分区，然后您将获得最新数据（按 DateCreated desc 排序）

    with CTE as (
 select [PrimaryKey], SecondaryKey, [Data],DateCreated,
        rn = row_number() over (partition by SecondaryKey order by DateCreated desc)
  from dbo.Entry
  )
 select [PrimaryKey], SecondaryKey, [Data],DateCreated
 From CTE where rn = 1 --rn = 1 is most recent data since we are ordering by desc

    见SQLFiddle

    • 1

相关问题