获取每组的前 1 行

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn
   FROM DocumentStatusLogs
)
SELECT *
FROM cte
WHERE rn = 1

如果您预计每天有 2 个条目，那么这将任意选择一个。若要在一天内获取这两个条目，请改用 DENSE_RANK

至于是否规范化，这取决于你是否想要：

• 在 2 个地方保持状态
• 保留状态历史记录
• ...

就目前而言，您可以保留状态历史记录。如果您也希望在父表中显示最新状态（即非规范化），则需要一个触发器来维护父表中的“状态”。或删除此状态历史记录表。

SELECT * FROM
DocumentStatusLogs JOIN (
  SELECT DocumentID, MAX(DateCreated) DateCreated
  FROM DocumentStatusLogs
  GROUP BY DocumentID
  ) max_date USING (DocumentID, DateCreated)

什么数据库服务器？此代码不适用于所有这些代码。

关于你问题的后半部分，在我看来，将状态作为一列包括在内似乎是合理的。您可以保留为日志，但仍将最新信息存储在主表中。DocumentStatusLogs

顺便说一句，如果您在“文档”表中已经有该列，则可以使用该列进行联接（只要在 中是唯一的）。DateCreatedDocumentStatusLogsDateCreatedDocumentStatusLogs

编辑：MsSQL不支持USING，因此将其更改为：

ON DocumentStatusLogs.DocumentID = max_date.DocumentID AND DocumentStatusLogs.DateCreated = max_date.DateCreated

我刚刚学会了如何使用.以下是在此方案中使用它的方法：cross apply

 select d.DocumentID, ds.Status, ds.DateCreated 
 from Documents as d 
 cross apply 
     (select top 1 Status, DateCreated
      from DocumentStatusLogs 
      where DocumentID = d.DocumentId
      order by DateCreated desc) as ds

在想要避免使用 row_count（） 的情况下，还可以使用左联接：

select ds.DocumentID, ds.Status, ds.DateCreated 
from DocumentStatusLogs ds
left join DocumentStatusLogs filter 
    ON ds.DocumentID = filter.DocumentID
    -- Match any row that has another row that was created after it.
    AND ds.DateCreated < filter.DateCreated
-- then filter out any rows that matched 
where filter.DocumentID is null 

对于示例架构，您还可以使用“不在子查询中”，它通常编译为与左联接相同的输出：

select ds.DocumentID, ds.Status, ds.DateCreated 
from DocumentStatusLogs ds
WHERE ds.ID NOT IN (
    SELECT filter.ID 
    FROM DocumentStatusLogs filter
    WHERE ds.DocumentID = filter.DocumentID
        AND ds.DateCreated < filter.DateCreated)

请注意，如果表没有至少一个单列唯一键/约束/索引（在本例中为主键“Id”），则子查询模式将不起作用。

这两个查询往往比 row_count（） 查询（由查询分析器测量）更“昂贵”。但是，您可能会遇到它们更快地返回结果或启用其他优化的情况。

我的代码从每个组中选择前 1 名

select a.* from #DocumentStatusLogs a where 
 datecreated in( select top 1 datecreated from #DocumentStatusLogs b
where 
a.documentid = b.documentid
order by datecreated desc
)

如果您担心性能，也可以使用以下方法执行此操作：MAX()

SELECT *
FROM DocumentStatusLogs D
WHERE DateCreated = (SELECT MAX(DateCreated) FROM DocumentStatusLogs WHERE ID = D.ID)

ROW_NUMBER()需要语句中的所有行，而不需要。应该会大大加快您的查询速度。SELECTMAX

这是一个相当古老的线程，但我认为我会投入两美分，因为接受的答案对我来说不是特别有效。我在大型数据集上尝试了 gbn 的解决方案，发现它非常慢（在 SQL Server 2012 中，500 多万条记录需要 >45 秒）。从执行计划来看，很明显问题在于它需要一个 SORT 操作，这会大大减慢速度。

这是我从实体框架中提取的替代方法，它不需要 SORT 操作并执行非聚集索引搜索。这会将上述记录集的执行时间缩短到 < 2 秒。

SELECT 
[Limit1].[DocumentID] AS [DocumentID], 
[Limit1].[Status] AS [Status], 
[Limit1].[DateCreated] AS [DateCreated]
FROM   (SELECT DISTINCT [Extent1].[DocumentID] AS [DocumentID] FROM [dbo].[DocumentStatusLogs] AS [Extent1]) AS [Distinct1]
OUTER APPLY  (SELECT TOP (1) [Project2].[ID] AS [ID], [Project2].[DocumentID] AS [DocumentID], [Project2].[Status] AS [Status], [Project2].[DateCreated] AS [DateCreated]
    FROM (SELECT 
        [Extent2].[ID] AS [ID], 
        [Extent2].[DocumentID] AS [DocumentID], 
        [Extent2].[Status] AS [Status], 
        [Extent2].[DateCreated] AS [DateCreated]
        FROM [dbo].[DocumentStatusLogs] AS [Extent2]
        WHERE ([Distinct1].[DocumentID] = [Extent2].[DocumentID])
    )  AS [Project2]
    ORDER BY [Project2].[ID] DESC) AS [Limit1]

现在我假设在原始问题中没有完全指定一些内容，但是如果您的表设计使您的 ID 列是自动递增 ID，并且每次插入时 DateCreated 都设置为当前日期，那么即使没有运行上面的查询，您实际上也可以通过对 ID 进行排序而不是在 DateCreated 上排序来获得 gbn 解决方案的相当大的性能提升（大约一半的执行时间）因为这将提供相同的排序顺序，并且排序速度更快。

我在这里对各种建议做了一些计时，结果实际上取决于所涉及的表的大小，但最一致的解决方案是使用 CROSS APPLY 这些测试是针对 SQL Server 2008-R2 运行的，使用一个包含 6,500 条记录的表和另一个包含 1.37 亿条记录的表（相同的架构）。被查询的列是表上主键的一部分，表宽度非常小（大约 30 个字节）。这些时间由 SQL Server 根据实际执行计划进行报告。

Query                                  Time for 6500 (ms)    Time for 137M(ms)

CROSS APPLY                                    17.9                17.9
SELECT WHERE col = (SELECT MAX(COL)…)           6.6               854.4
DENSE_RANK() OVER PARTITION                     6.6               907.1

我认为真正令人惊奇的是，无论涉及多少行，CROSS APPLY 的时间都是如此一致。

从上面验证克林特令人敬畏的正确答案：

下面两个查询之间的性能很有意思。52%位居榜首。48%是第二个。使用 DISTINCT 而不是 ORDER BY 将性能提高了 4%。但 ORDER BY 具有按多列排序的优势。

IF (OBJECT_ID('tempdb..#DocumentStatusLogs') IS NOT NULL) BEGIN DROP TABLE #DocumentStatusLogs END

CREATE TABLE #DocumentStatusLogs (
    [ID] int NOT NULL,
    [DocumentID] int NOT NULL,
    [Status] varchar(20),
    [DateCreated] datetime
)

INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (2, 1, 'S1', '7/29/2011 1:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (3, 1, 'S2', '7/30/2011 2:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (6, 1, 'S1', '8/02/2011 3:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (1, 2, 'S1', '7/28/2011 4:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (4, 2, 'S2', '7/30/2011 5:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (5, 2, 'S3', '8/01/2011 6:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (6, 3, 'S1', '8/02/2011 7:00:00')

选项 1：

    SELECT
    [Extent1].[ID], 
    [Extent1].[DocumentID],
    [Extent1].[Status], 
    [Extent1].[DateCreated]
FROM #DocumentStatusLogs AS [Extent1]
    OUTER APPLY (
        SELECT TOP 1
            [Extent2].[ID], 
            [Extent2].[DocumentID],
            [Extent2].[Status], 
            [Extent2].[DateCreated]
        FROM #DocumentStatusLogs AS [Extent2]
        WHERE [Extent1].[DocumentID] = [Extent2].[DocumentID]
        ORDER BY [Extent2].[DateCreated] DESC, [Extent2].[ID] DESC
    ) AS [Project2]
WHERE ([Project2].[ID] IS NULL OR [Project2].[ID] = [Extent1].[ID])

选项 2：

SELECT 
    [Limit1].[DocumentID] AS [ID], 
    [Limit1].[DocumentID] AS [DocumentID], 
    [Limit1].[Status] AS [Status], 
    [Limit1].[DateCreated] AS [DateCreated]
FROM (
    SELECT DISTINCT [Extent1].[DocumentID] AS [DocumentID] FROM #DocumentStatusLogs AS [Extent1]
) AS [Distinct1]
    OUTER APPLY  (
        SELECT TOP (1) [Project2].[ID] AS [ID], [Project2].[DocumentID] AS [DocumentID], [Project2].[Status] AS [Status], [Project2].[DateCreated] AS [DateCreated]
        FROM (
            SELECT 
                [Extent2].[ID] AS [ID], 
                [Extent2].[DocumentID] AS [DocumentID], 
                [Extent2].[Status] AS [Status], 
                [Extent2].[DateCreated] AS [DateCreated]
            FROM #DocumentStatusLogs AS [Extent2]
            WHERE [Distinct1].[DocumentID] = [Extent2].[DocumentID]
        )  AS [Project2]
        ORDER BY [Project2].[ID] DESC
    ) AS [Limit1]

在 SQL Server Management Studio Microsoft：突出显示并运行第一个块后，突出显示选项 1 和选项 2，右键单击 -> [显示估计的执行计划]。然后运行整个过程以查看结果。

选项 1 结果：

ID  DocumentID  Status  DateCreated
6   1   S1  8/2/11 3:00
5   2   S3  8/1/11 6:00
6   3   S1  8/2/11 7:00

选项 2 结果：

ID  DocumentID  Status  DateCreated
6   1   S1  8/2/11 3:00
5   2   S3  8/1/11 6:00
6   3   S1  8/2/11 7:00

注意：

当我希望连接是 1 到 （1 of many ）时，我倾向于使用 APPLY。

如果我希望联接是一对多或多对多，则使用 JOIN。

我避免使用 ROW_NUMBER（） 进行 CTE，除非我需要做一些高级操作并且对窗口性能损失感到满意。

我还避免在 WHERE 或 ON 子句中使用 EXISTS / IN 子查询，因为我经历过这种情况导致一些糟糕的执行计划。但里程数各不相同。在需要的时间和地点查看执行计划并分析性能！

试试这个：

SELECT [DocumentID]
    ,[tmpRez].value('/x[2]', 'varchar(20)') AS [Status]
    ,[tmpRez].value('/x[3]', 'datetime') AS [DateCreated]
FROM (
    SELECT [DocumentID]
        ,cast('<x>' + max(cast([ID] AS VARCHAR(10)) + '</x><x>' + [Status] + '</x><x>' + cast([DateCreated] AS VARCHAR(20))) + '</x>' AS XML) AS [tmpRez]
    FROM DocumentStatusLogs
    GROUP BY DocumentID
    ) AS [tmpQry]

SELECT o.*
FROM `DocumentStatusLogs` o                   
  LEFT JOIN `DocumentStatusLogs` b                   
  ON o.DocumentID = b.DocumentID AND o.DateCreated < b.DateCreated
 WHERE b.DocumentID is NULL ;

如果只想按 DateCreated 返回最近的文档顺序，它将仅按 DocumentID 返回前 1 个文档

这是关于这个话题最容易找到的问题之一，所以我想给它一个现代的答案（既供我参考，也为了帮助他人）。通过使用 and，您可以对上述查询进行简短的工作：first_valueover

Select distinct DocumentID
  , first_value(status) over (partition by DocumentID order by DateCreated Desc) as Status
  , first_value(DateCreated) over (partition by DocumentID order by DateCreated Desc) as DateCreated
From DocumentStatusLogs

这应该适用于 SQL Server 2012 及更高版本。 可以认为是使用子句时完成的一种方式。 允许在选择列表中进行分组，因此无需编写嵌套子查询（就像许多现有答案那样），而是以更具可读性的方式进行。First_valueSelect Top 1overOver

我知道这是一个旧线程，但解决方案非常好，可能对一些阅读解决方案有所帮助。TOP 1 WITH TIES

select top 1 with ties
   DocumentID
  ,Status
  ,DateCreated
from DocumentStatusLogs
order by row_number() over (partition by DocumentID order by DateCreated desc)

该子句告知 SQL Server 要返回每个组的第一行。但是，SQL Server 如何知道如何对数据进行分组呢？这就是它的用武之地。后面的列定义 SQL Server 如何对数据进行分组。在每个组中，将根据列对行进行排序。排序后，将在查询中返回每个组中的第一行。select top 1 with tiesorder by row_number() over (partition by DocumentID order by DateCreated descpartition byorder by

有关 TOP 条款的更多信息，请参见此处。

此解决方案可用于获取每个分区的 TOP N 最新行（在示例中，WHERE 语句中的 N 为 1，分区为 doc_id）：

SELECT T.doc_id, T.status, T.date_created FROM 
(
    SELECT a.*, ROW_NUMBER() OVER (PARTITION BY doc_id ORDER BY date_created DESC) AS rnk FROM doc a
) T
WHERE T.rnk = 1;

以下是解决手头问题的 3 种独立方法，以及每个查询的最佳索引选择（请自己尝试索引并查看逻辑读取、运行时间、执行计划。我已经根据我的经验提供了关于此类查询的建议，但没有针对此特定问题执行）。

方法 1：使用 ROW_NUMBER（）。如果行存储索引无法提高性能，则可以尝试使用非聚集/聚集列存储索引，因为对于具有聚合和分组的查询，以及始终按不同列排序的表，列存储索引通常是最佳选择。

;WITH CTE AS
    (
       SELECT   *,
                RN = ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC)
       FROM     DocumentStatusLogs
    )
    SELECT  ID      
        ,DocumentID 
        ,Status     
        ,DateCreated
    FROM    CTE
    WHERE   RN = 1;

方法 2：使用 FIRST_VALUE。如果行存储索引无法提高性能，则可以尝试使用非聚集/聚集列存储索引，因为对于具有聚合和分组的查询，以及始终按不同列排序的表，列存储索引通常是最佳选择。

SELECT  DISTINCT
    ID      = FIRST_VALUE(ID) OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC)
    ,DocumentID
    ,Status     = FIRST_VALUE(Status) OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC)
    ,DateCreated    = FIRST_VALUE(DateCreated) OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC)
FROM    DocumentStatusLogs;

方法 3：使用 CROSS APPLY。在涵盖查询中使用的列的 DocumentStatusLogs 表上创建行存储索引应该足以涵盖查询，而无需列存储索引。

SELECT  DISTINCT
    ID      = CA.ID
    ,DocumentID = D.DocumentID
    ,Status     = CA.Status 
    ,DateCreated    = CA.DateCreated
FROM    DocumentStatusLogs D
    CROSS APPLY (
            SELECT  TOP 1 I.*
            FROM    DocumentStatusLogs I
            WHERE   I.DocumentID = D.DocumentID
            ORDER   BY I.DateCreated DESC
            ) CA;

SELECT documentid, 
       status, 
       datecreated 
FROM   documentstatuslogs dlogs 
WHERE  status = (SELECT status 
                 FROM   documentstatuslogs 
                 WHERE  documentid = dlogs.documentid 
                 ORDER  BY datecreated DESC 
                 LIMIT  1) 

CROSS APPLY是我用于解决方案的方法，因为它对我和我的客户需求都有效。从我所读到的内容来看，如果他们的数据库大幅增长，应该提供最佳的整体性能。

我相信这可以像这样完成。这可能需要一些调整，但您可以从组中选择最大值。

这些答案是矫枉过正的。

SELECT
  d.DocumentID,
  MAX(d.Status),
  MAX(d1.DateCreated)
FROM DocumentStatusLogs d, DocumentStatusLogs d1
USING DocumentID
GROUP BY 1
ORDER BY 3 DESC

一些数据库引擎*开始支持允许过滤窗口函数结果的子句（接受的答案使用该子句）。QUALIFY

所以公认的答案可以变成

SELECT *, ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn
FROM DocumentStatusLogs
QUALIFY rn = 1

有关深入解释，请参阅本文：https://jrandrews.net/the-joy-of-qualify

您可以使用此工具查看哪个数据库支持此子句： https://www.jooq.org/translate/ 当目标方言不支持限定子句时，可以选择转换该子句。

*Teradata、BigQuery、H2、Snowflake...