数据表(sns):
sn lastlogin
----------------------
456 2010-01-01
123 2010-02-01
123 2010-02-02
123 2010-02-03
123 2010-02-04
123 2010-02-05
123 2010-02-06
. .
. .
. .要求每个 sn 最多保留5条记录,将大于5条记录的sn的最早登录的记录删除
SQL语句该怎么写?
谢谢!
sn lastlogin
----------------------
456 2010-01-01
123 2010-02-01
123 2010-02-02
123 2010-02-03
123 2010-02-04
123 2010-02-05
123 2010-02-06
. .
. .
. .要求每个 sn 最多保留5条记录,将大于5条记录的sn的最早登录的记录删除
SQL语句该怎么写?
谢谢!
(select * from t_lindabang a where 5<=(select count(*) from t_lindabang where sn=a.sn and lastlogin>a.lastlogin)) b
where t.sn=b.sn and t.lastlogin=b.lastloginmysql> select * from t_lindabang;
+------+------------+
| sn | lastlogin |
+------+------------+
| 456 | 2010-01-01 |
| 123 | 2010-02-01 |
| 123 | 2010-02-02 |
| 123 | 2010-02-03 |
| 123 | 2010-02-04 |
| 123 | 2010-02-05 |
| 123 | 2010-02-06 |
+------+------------+
7 rows in set (0.00 sec)mysql> delete t from t_lindabang t,
-> (select * from t_lindabang a where 5<=(select count(*) from t_lindabangwhere sn=a.sn and lastlogin>a.lastlogin)) b
-> where t.sn=b.sn and t.lastlogin=b.lastlogin;
Query OK, 1 row affected (0.06 sec)mysql> select * from t_lindabang;
+------+------------+
| sn | lastlogin |
+------+------------+
| 456 | 2010-01-01 |
| 123 | 2010-02-02 |
| 123 | 2010-02-03 |
| 123 | 2010-02-04 |
| 123 | 2010-02-05 |
| 123 | 2010-02-06 |
+------+------------+
6 rows in set (0.00 sec)mysql>