我有一个log
表,我要删除除最后三个记录以外的每个用户的记录。
架构图
DROP TABLE IF EXISTS `log`;
CREATE TABLE `log` (
`user_id` int(11) DEFAULT NULL,
`timestamp` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into `log`(`user_id`,`timestamp`) values (1,1389257013),(1,1389257014),(1,1389257015),(1,1389257016),(1,1389257017),(2,1389257018),(2,1389257019),(2,1389257020),(2,1389257021),(2,1389257022),(3,1389257023),(3,1389257024);
当前表:
id timestamp
1 1389257013
1 1389257014
1 1389257015
1 1389257016
1 1389257017
2 1389257018
2 1389257019
2 1389257020
2 1389257021
2 1389257022
3 1389257023
3 1389257024
预期表
id timestamp
1 1389257015
1 1389257016
1 1389257017
2 1389257020
2 1389257021
2 1389257022
3 1389257023
3 1389257024
请尝试以下SQL:
DELETE FROM log WHERE find_in_set(
TIMESTAMP, (
SELECT group_concat(t3) t4 FROM (
SELECT 1 AS dummy,
replace(group_concat(TIMESTAMP ORDER BY TIMESTAMP DESC), concat(SUBSTRING_INDEX(group_concat(TIMESTAMP ORDER BY TIMESTAMP DESC), ',', 3), ','), '') t3
FROM log
GROUP BY user_id HAVING count(*) > 3
) a GROUP BY dummy
)
)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句