主页 / dba / 问题 / 15583
Accepted
UB01
Asked: 2012-03-27 08:26:20 +0800 CST

将结果限制为前 2 个排名行

  • 772

在 SQL Server 2008 中，我RANK() OVER (PARTITION BY Col2 ORDER BY Col3 DESC)使用RANK. 但是我每个分区都有数百条记录，所以我会从排名 1、2、3......999 中获取值。但我只希望每个最多 2RANKs个PARTITION。

例子：

ID   Name    Score    Subject
1    Joe      100      Math
2    Jim      99       Math
3    Tim      98       Math
4    Joe      99       History
5    Jim      100      History
6    Tim      89       History
7    Joe      80       Geography
8    Tim      100      Geography
9    Jim      99       Geography

我希望结果是：

SELECT Subject, Name, RANK() OVER (PARTITION BY Subject ORDER BY Score DESC)
FROM Table

Subject        Name      Rank
Math           Joe        1
Math           Jim        2
History        Jim        1
History        Joe        2
Geography      Tim        1
Geography      Jim        2

我只想在每个类别中排名 1 和 2。我该怎么做呢？

sql-server sql-server-2008

4 个回答

  1. select * from (
SELECT Subject, Name, RANK() OVER (PARTITION BY Subject ORDER BY Score DESC) as RN
FROM Table
) a
where a.RN <= 2
    • 19
  2. Best Answer

    您可以将原始查询 usingrank()放入子查询中，并用过滤结果的查询包装它。

    • 15

  3. 我认为在 SQL Server 中执行此操作的方法是将窗口函数与公共表表达式结合起来：

    with cte as (
SELECT Subject, Name, RANK() OVER (PARTITION BY Subject ORDER BY Score DESC) as ordinal
FROM Table
)
select * from cte where ordinal <= 2
    • 0

  4. 对于 Teradara，您也可以这样做：

    SELECT 
Subject, 
Name, 
RANK() OVER (PARTITION BY Subject ORDER BY Score DESC) as RN
FROM Table
QUALIFY a.RN <= 2
    • -1

相关问题