root@localhost : test 02:18:41>select * from us; +-------+------+-------+---------------------+ | rowid | user | score | testtime | +-------+------+-------+---------------------+ | 1 | a | 80 | 2012-09-03 14:11:31 | | 2 | a | 60 | 2012-09-03 14:11:53 | | 3 | a | 75 | 2012-09-03 14:11:57 | | 4 | b | 59 | 2012-09-03 14:12:14 | | 5 | b | 69 | 2012-09-03 14:12:22 | | 6 | b | 79 | 2012-09-03 14:12:42 | | 7 | c | 90 | 2012-09-03 14:12:57 | | 8 | c | 95 | 2012-09-03 14:13:04 | | 9 | d | 85 | 2012-09-03 14:13:13 | +-------+------+-------+---------------------+ 9 rows in set (0.00 sec)root@localhost : test 02:18:46>select a.* from us a where 2>(select count(*) from us b where a.user=b.user and b.testtime > a.testtime ); +-------+------+-------+---------------------+ | rowid | user | score | testtime | +-------+------+-------+---------------------+ | 2 | a | 60 | 2012-09-03 14:11:53 | | 3 | a | 75 | 2012-09-03 14:11:57 | | 5 | b | 69 | 2012-09-03 14:12:22 | | 6 | b | 79 | 2012-09-03 14:12:42 | | 7 | c | 90 | 2012-09-03 14:12:57 | | 8 | c | 95 | 2012-09-03 14:13:04 | | 9 | d | 85 | 2012-09-03 14:13:13 | +-------+------+-------+---------------------+
贴建表及插入记录的SQL,及要求结果出来看看
分组取前两条吧 SELECT a.rowid,a.user,a.score,a.testtime FROM score_us a LEFT JOIN score_us b ON a.user = b.user AND a.testtime < b.testtime GROUP BY a.rowid,a.user,a.score,a.testtime HAVING COUNT(b.rowid) < 2 ORDER BY a.testtime
root@localhost : test 02:18:46>select a.* from us a where 2>(select count(*) from us b where a.user=b.user and b.testtime > a.testtime ); + 这个可以
[征集]分组取最大N条记录方法征集,及散分....
+-------+------+-------+---------------------+
| rowid | user | score | testtime |
+-------+------+-------+---------------------+
| 1 | a | 80 | 2012-09-03 14:11:31 |
| 2 | a | 60 | 2012-09-03 14:11:53 |
| 3 | a | 75 | 2012-09-03 14:11:57 |
| 4 | b | 59 | 2012-09-03 14:12:14 |
| 5 | b | 69 | 2012-09-03 14:12:22 |
| 6 | b | 79 | 2012-09-03 14:12:42 |
| 7 | c | 90 | 2012-09-03 14:12:57 |
| 8 | c | 95 | 2012-09-03 14:13:04 |
| 9 | d | 85 | 2012-09-03 14:13:13 |
+-------+------+-------+---------------------+
9 rows in set (0.00 sec)root@localhost : test 02:18:46>select a.* from us a where 2>(select count(*) from us b where a.user=b.user and b.testtime > a.testtime );
+-------+------+-------+---------------------+
| rowid | user | score | testtime |
+-------+------+-------+---------------------+
| 2 | a | 60 | 2012-09-03 14:11:53 |
| 3 | a | 75 | 2012-09-03 14:11:57 |
| 5 | b | 69 | 2012-09-03 14:12:22 |
| 6 | b | 79 | 2012-09-03 14:12:42 |
| 7 | c | 90 | 2012-09-03 14:12:57 |
| 8 | c | 95 | 2012-09-03 14:13:04 |
| 9 | d | 85 | 2012-09-03 14:13:13 |
+-------+------+-------+---------------------+
FROM score_us a
LEFT JOIN score_us b
ON a.user = b.user AND a.testtime < b.testtime
GROUP BY a.rowid,a.user,a.score,a.testtime
HAVING COUNT(b.rowid) < 2
ORDER BY a.testtime
+
这个可以