求条sql统计语句

Question

id      usernam       addtime
1 张三     2008-12-14 14:36:00
2 李四     2008-12-14 14:49:00
3 刘五     2008-12-14 20:35:00
4 陈六     2008-12-14 21:43:00
5 陈七     2008-12-15 20:43:00
6 陈八     2008-12-15 20:49:00
7 陈九     2008-12-15 21:02:00
8 陈十     2008-12-15 21:13:00
想获得某一用户在 当天 的排序名次,如: 陈九 2008-12-15 排在第三位谢谢.

Answer 1

mysql> select * from t_free212;
+----+----------+---------------------+
| id | username | addtime             |
+----+----------+---------------------+
|  1 | 张三     | 2008-12-14 14:36:00 |
|  2 | 李四     | 2008-12-14 14:49:00 |
|  3 | 刘五     | 2008-12-14 20:35:00 |
|  4 | 陈六     | 2008-12-14 21:43:00 |
|  5 | 陈七     | 2008-12-15 20:43:00 |
|  6 | 陈八     | 2008-12-15 20:49:00 |
|  7 | 陈九     | 2008-12-15 21:02:00 |
|  8 | 陈十     | 2008-12-15 21:13:00 |
+----+----------+---------------------+
8 rows in set (0.00 sec)mysql> select count(*)+1
    -> from t_free212
    -> where addtime > '2008-12-15 00:00:00'
    -> and addtime<(select addtime from t_free212 where date(addtime)='2008-12-15' and username='陈九');
+------------+
| count(*)+1 |
+------------+
|          3 |
+------------+
1 row in set (0.00 sec)mysql>