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

SQL Server 作者: Rector 56006阅读 0评论 0收藏 收藏本文

郑重申明:本文未经许可,禁止任何形式转载

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条记录

发布于: 2014-07-05 23:56:14
分享扩散:

文章评论

获取验证码