14.| Lily | 100 | 4 |
15.| Lucy | 146 | 1 |
16.| Lucy | 137 | 2 |
17.| Lucy | 127 | 3 |
18.| Lucy | 121 | 4 |
19.| Lucy | 104 | 5 |
20.| Lucy | 100 | 6 |
21.| ytt | 149 | 1 |
22.| ytt | 149 | 1 |
23.| ytt | 117 | 3 |
24.| ytt | 116 | 4 |
25.| ytt | 116 | 4 |
26.| ytt | 106 | 6 |
27.+----------+------+----------+
28.17 rows in set (0.02 sec)
mysql>
select a.username, a.rank, find_in_set(a.rank,b.rank_gp) as rank_cnt
from group_concat as a ,
(select username,group_concat(rank order by rank desc separator ',') as rank_gp from group_concat group by username
) b
where a.username = b.username order by a.username asc,a.rank desc;
+----------+------+----------+
| 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 in set (0.02 sec)
当然了，如果MySQL SQL不太熟悉，可以用程序来处理，比如我下面用python 实现了rank 函数，执行结果如下：（脚本源代码最后）
[sql] view plaincopyprint 01.>>> ================================ RESTART ================================
02.>>>
03. username | rank | rank_cnt
04.--------------------------------
05.ytt |149 |1
07.ytt |117 |3
08.ytt |116 |4
09.ytt |116 |4
10.ytt |106 |6
11.Lucy |146 |1
12.Lucy |137 |2
13.Lucy |127 |3
14.Lucy |121 |4
15.Lucy |104 |5
16.Lucy |100 |6
17.Lily |136 |1
18.Lily |136 |2
19.Lily |105 |3
20.Lily |100 |4
21.Lily |100 |4
22.(17 Rows.)
23.Time: 0.162 Seconds.
>>> ================================ RESTART ================================
>>>
username | rank | rank_cnt
--------------------------------
ytt |149 |1
ytt |149 |1
ytt |117 |3
ytt |116 |4
ytt |116 |4
ytt |106 |6
Lucy |146 |1
Lucy |137 |2
Lucy |127 |3
Lucy |121 |4
Lucy |104 |5
Lucy |100 |6
Lily |136 |1
Lily |136 |2
Lily |105 |3
Lily |100 |4
Lily |100 |4
(17 Rows.)
Time: 0.162 Seconds.
附上脚本代码：
[python] view plaincopyprint 01.from __future__ import print_function
02.from datetime import date, datetime, timedelta
03.import mysql.connector
04.import time
05.# Created by ytt 2014/5/14.
06.# Rank function implement.
07.def db_connect(is_true):
08. cnx = mysql.