Title: mysql 查询x表posts字段,删除重复的值&同一日的数据只保留posts最大的那条 [Print this page]
Author:
sky999 Time: 2023-7-16 14:53 Title: mysql 查询x表posts字段,删除重复的值&同一日的数据只保留posts最大的那条
mysql 查询x表posts字段,删除重复的值
DELETE FROM X
WHERE id NOT IN (
SELECT * FROM (
SELECT MAX(id)
FROM X
GROUP BY posts
) AS temp
);
day字段类型为timestamp，同一日的数据只保留posts最大的那条。
DELETE t1
FROM X t1
JOIN (
SELECT DATE(day) AS date, MAX(posts) AS max_posts
FROM X
GROUP BY DATE(day)
) t2 ON DATE(t1.day) = t2.date AND t1.posts < t2.max_posts;
[ 本帖最后由 sky999 于 2023-7-16 15:01 编辑 ]
| Welcome AbyssalSwamp (http://service.caffz.com:12345/mud/AbyssalSwamp/index/) |
caffz.com |