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

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