SQL SERVER 2005或更高版本实现分组后取TOP N条记录

SQL Server 2014-07-05 55893浏览 收藏本文

SQL SERVER 2005或更高版本实现分组后取TOP N条记录,参考地址:http://rickosborne.org/blog/2008/01/sql-getting-top-n-rows-for-a-grouped-query/

Table: girl
id name hair score
1 Kim Brunette 8
2 Anne Brunette 7
3 Sarah Brunette 10
4 Deborah Brunette 9
5 Mia Brunette 5
6 Samantha Brunette 0
7 Jo Ann Blonde 7
8 Katie Blonde 8
9 Becca Blonde 9
10 Mini Blonde 5
11 Lauren Blonde 4
12 Kit Blonde 10
SQL查询语句如下:
SELECT c.*, d.ranknum
FROM girl AS c
  INNER JOIN (
    SELECT a.id, COUNT(*) AS ranknum
    FROM girl AS a
      INNER JOIN girl AS b ON (a.hair = b.hair) AND (a.score <= b.score)
    GROUP BY a.id
    HAVING COUNT(*) <= 3
  ) AS d ON (c.id = d.id)
ORDER BY c.hair, d.ranknum
查询得到结果为:
id name hair score ranknum
12 Kit Blonde 10 1
9 Becca Blonde 9 2
8 Katie Blonde 8 3
3 Sarah Brunette 10 1
4 Deborah Brunette 9 2
1 Kim Brunette 8 3

转载请注明:图享网 » SQL SERVER 2005或更高版本实现分组后取TOP N条记录