sql - 使用 SQL Server 2005 检索每个组的 TOP X 成员

Question

我已经看到这个问题被问过几次了，我也写了自己的查询，但是速度很慢，如果有人可以提供有关如何加快速度的建议，我将不胜感激。

在一个简化的场景中，我有以下两个表:

集团
- GroupID(主键)

成员
- MemberID(主键)
- GroupID(外键)

假设，对于 Group 中的每个 GroupID，我想从具有该 GroupID 的 Member 中找到前 2 个 MemberID 值。

这是我当前有效的查询，但速度非常慢:

SELECT M.MemberID, M.GroupID
FROM   Member AS M
WHERE  M.MemberID in 
        (Select top 2 Member.MemberID
         FROM Member
         Where Member.GroupID = M.GroupID
         ORDER BY Member.MemberID)

假设组有以下行
群组ID
1
2
3

Member 有以下几行
成员(member)ID、群组ID
1, 1
2, 2
3, 3
4, 1
5, 2
6, 3
7, 1
8, 2
9、3

那么我的查询应该返回:
成员(member)ID 群组ID
1, 1
2, 2
3, 3
4, 1
5, 2
6、3

Answer 1

我相信依赖嵌套查询对于数据库引擎来说可能真的很难优化好(尽管@John Saunders 要求查看执行计划是有根据的，并且查看您拥有的索引也不会造成伤害；-)。

但是，在 SQL Server 2005 和 2008(以及其他 SQL 引擎，因为该功能符合最新的 ANSI 标准)中，一种更自然的解决此类排名相关问题的方法是排名函数 -- RANK, DENSE_RANK 或 ROW_NUMBER...无论如何，当您按唯一字段排名时，它们都是等价的；-)。即使除了优化之外，一旦您习惯了它们，它们也更容易阅读(当您的问题比这个更难时，它们会更强大)，尤其是在另一个简洁的新结构的帮助下， WITH 子句...:

WITH OrderedMembers AS
(
    SELECT MemberId, GroupId,
    ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY MemberId) AS RowNumber
    FROM Member 
) 
SELECT MemberId, GroupId
FROM OrderedMembers 
WHERE RowNumber <= 2
ORDER BY MemberId;