获取每组的前1行

Question

获取每组的前1行

我有一张桌子，我想得到每一组的最新条目。这张桌子：

DocumentStatusLogs表

|ID| DocumentID | Status | DateCreated |

| 2| 1          | S1     | 7/29/2011   |

| 3| 1          | S2     | 7/30/2011   |

| 6| 1          | S1     | 8/02/2011   |

| 1| 2          | S1     | 7/28/2011   |

| 4| 2          | S2     | 7/30/2011   |

| 5| 2          | S3     | 8/01/2011   |

| 6| 3          | S1     | 8/02/2011   |

这张表将按DocumentID并按DateCreated按降序排列。每人DocumentID我想知道最新的情况。

我喜欢的输出：

| DocumentID | Status | DateCreated |

| 1          | S1     | 8/02/2011   |

| 2          | S3     | 8/01/2011   |

| 3          | S1     | 8/02/2011   |

是否有任何聚合函数只从每个组中获得顶部？见伪码GetOnlyTheTop下文：

SELECT

  DocumentID,

  GetOnlyTheTop(Status),

  GetOnlyTheTop(DateCreated)

FROM DocumentStatusLogs

GROUP BY DocumentID

ORDER BY DateCreated DESC

如果不存在这样的函数，有什么方法可以实现我想要的输出？

或者首先，这可能是由于数据库不规范造成的吗？我在想，因为我要找的只是一排，是不是status是否也位于父表中？

有关更多信息，请参见父表：

电流Documents表

| DocumentID | Title  | Content  | DateCreated |

| 1          | TitleA | ...      | ...         |

| 2          | TitleB | ...      | ...         |

| 3          | TitleC | ...      | ...         |

父表是否应该是这样的，这样我就可以轻松地访问它的状态了吗？

| DocumentID | Title  | Content  | DateCreated | CurrentStatus |

| 1          | TitleA | ...      | ...         | s1            |

| 2          | TitleB | ...      | ...         | s3            |

| 3          | TitleC | ...      | ...         | s1            |

更新我刚刚学会了如何使用“应用”，这使它更容易解决这些问题。

Answer 1

;WITH cte AS(    SELECT *,          ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn   FROM DocumentStatusLogs)SELECT *FROM cteWHERE rn = 1如果您期望每天有两个条目，那么这将任意选择一个。若要获得这两个条目一天，请使用密集_秩代替至于正常化与否，取决于你是否想：在2个地方保持状态保存状态历史...就目前情况而言，你保留了身份历史。如果您也想在父表中获得最新的状态(这是去角色化)，那么您需要一个触发器来维护父表中的“状态”。或删除此状态历史记录表。

Answer 2

我刚学会了如何使用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

Answer 3

我在这里对各种建议做了一些时间安排，结果实际上取决于所涉及的表的大小，但最一致的解决方案是使用交叉应用-这些测试是针对SQLServer2008-R2运行的，使用的是一个记录为6500条的表，以及另一个记录为1.37亿条的表(相同的模式)。正在查询的列是表中主键的一部分，表的宽度非常小(约30字节)。SQLServer根据实际执行计划报告时间。Query                                  Time for 6500 (ms)    Time for 137M(ms)CROSS APPLY                                    17.9                17.9SELECT WHERE col = (SELECT MAX(COL)…)           6.6               854.4DENSE_RANK() OVER PARTITION                     6.6               907.1我认为真正令人惊讶的是，无论涉及的行数如何，交叉应用的时间是多么一致。