大部分数据库都提供了窗口函数，比如RANK,ROW_NUMBER等等。 MySQL 这方面没有直接提供，但是可以变相的实现，我以前写了row_number 的实现，今天有时间把 rank 的实现贴出来。
这里，我用MySQL 以及Python 分别实现了rank 窗口函数。
原始表信息：
[sql] view plaincopyprint 01.t_girl=# \d group_concat;
02. Table "ytt.group_concat"
03. Column | Type | Modifiers
04.----------+-----------------------+-----------
05. rank | integer |
06. username | character varying(20) |
t_girl=# \d group_concat;
Table "ytt.group_concat"
Column | Type | Modifiers
----------+-----------------------+-----------
rank | integer |
username | character varying(20) |
表数据
[sql] view plaincopyprint 01.t_girl=# select * from group_concat;
02. rank | username
03.------+----------
04. 100 | Lucy
05. 127 | Lucy
06. 146 | Lucy
07. 137 | Lucy
08. 104 | Lucy
09. 121 | Lucy
10. 136 | Lily
11. 100 | Lily
12. 100 | Lily
13. 105 | Lily
14. 136 | Lily
15. 149 | ytt
16. 116 | ytt
17. 116 | ytt
18. 149 | ytt
19. 106 | ytt
20. 117 | ytt
21.(17 rows)
22.
23.
24.Time: 0.638 ms
t_girl=# select * from group_concat;
rank | username
------+----------
100 | Lucy
127 | Lucy
146 | Lucy
137 | Lucy
104 | Lucy
121 | Lucy
136 | Lily
100 | Lily
100 | Lily
105 | Lily
136 | Lily
149 | ytt
116 | ytt
116 | ytt
149 | ytt
106 | ytt
117 | ytt
(17 rows)
Time: 0.638 ms
PostgreSQL 的rank 窗口函数示例：
[sql] view plaincopyprint 01.t_girl=# select username,rank,rank() over(partition by username order by rank desc) as rank_cnt from group_concat;
02. username | rank | rank_cnt
03.----------+------+----------
04. Lily | 136 | 1
05. Lily | 136 | 1
06. Lily | 105 | 3
07. Lily | 100 | 4
08. Lily | 100 | 4
09. Lucy | 146 | 1
10. Lucy | 137 | 2
11. Lucy | 127 | 3
12. Lucy | 121 | 4
13. Lucy | 104 | 5
14. Lucy | 100 | 6
15. ytt | 149 | 1
16. ytt | 149 | 1
17. ytt | 117 | 3
18. ytt | 116 | 4
19. ytt | 116 | 4
20. ytt | 106 | 6
21.(17 rows)
22.
23.
24.Time: 131.150 ms
t_girl=# select username,rank,rank() over(partition by username order by rank desc) as rank_cnt from group_concat;
username | rank | rank_cnt
----------+------+----------
Lily | 136 | 1
Lily | 136 | 1
Lily | 105 | 3
Lily | 100 | 4
Lily | 100 | 4
Lucy | 146 | 1
Lucy | 137 | 2
Lucy | 127 | 3
Lucy | 121 | 4
Lucy | 104 | 5
Lucy | 100 | 6
ytt | 149 | 1
ytt | 149 | 1
ytt | 117 | 3
ytt | 116 | 4
ytt | 116 | 4
ytt | 106 | 6
(17 rows)
Time: 131.150 ms
MySQL 提供了group_concat 聚合函数可以变相的实现：
[sql] view plaincopyprint 01.mysql>
02.select a.username, a.rank, find_in_set(a.rank,b.rank_gp) as rank_cnt
03.from group_concat as a ,
04.(select username,group_concat(rank order by rank desc separator ',') as rank_gp from group_concat group by username
05.) b
06.where a.username = b.username order by a.username asc,a.rank desc;
07.+----------+------+----------+
08.| username | rank | rank_cnt |
09.+----------+------+----------+
10.| Lily | 136 | 1 |
11.| Lily | 136 | 1 |
12.| Lily | 105 | 3 |
13.| Lily | 100 |