userid score ID
100 3 1
101 4 1
102 5 1
103 6 1
104 7 1
105 1 2
106 2 2
107 3 2
108 4 2
109 5 2
120 1 3
121 2 3
122 4 3
123 6 3
124 7 3
以上表中 怎样通过sql取得不同ID下得分前三的userid
100 3 1
101 4 1
102 5 1
103 6 1
104 7 1
105 1 2
106 2 2
107 3 2
108 4 2
109 5 2
120 1 3
121 2 3
122 4 3
123 6 3
124 7 3
以上表中 怎样通过sql取得不同ID下得分前三的userid
mysql> select id, userid, score from tscore a where 3 >(select count(*) from tscore b where b.id=a.id and b.score>a.score) order by a.id, score desc;
+------+--------+-------+
| id | userid | score |
+------+--------+-------+
| 1 | 104 | 7 |
| 1 | 103 | 6 |
| 1 | 102 | 5 |
| 2 | 109 | 5 |
| 2 | 108 | 4 |
| 2 | 107 | 3 |
| 3 | 124 | 7 |
| 3 | 123 | 6 |
| 3 | 122 | 4 |
+------+--------+-------+
9 rows in set (0.01 sec)
[征集]分组取最大N条记录方法征集,及散分....