SQL 删除重复数据,重复数据只保留ID最小的行
程序员文章站
2022-06-02 16:01:45
...
删除重复数据,重复数据只保留ID最小的行
DELETE
FROM
t_customer_comment
WHERE
id IN (
SELECT
*
FROM
(
SELECT
id
FROM
t_customer_comment
WHERE
remaintain_order_id IN (
SELECT
remaintain_order_id
FROM
t_customer_comment
GROUP BY
remaintain_order_id
HAVING
count(remaintain_order_id) > 1
ORDER BY
count(remaintain_order_id) DESC
)
AND id NOT IN (
SELECT
min(id)
FROM
t_customer_comment t
GROUP BY
remaintain_order_id
HAVING
count(remaintain_order_id) > 1
ORDER BY
min(id) ASC
)
) AS ttt
)
思路:
1:使用 group by ... having 查找出重复的记录 a
2:使用select min(id) group by ...having count(...)>1找出重复数据中id最小的记录 b
3:找出 in a and not in b的数据行,即为需要删除的数据c;
4:直接使用delete from c 在非ORACEL下会报错:[Err] 1093 - You can't specify target table 't_customer_comment' for update in FROM clause;此时需要在c的外边套一层 select * from c as d
5:然后 delete from d ;删除成功!