SQL---mysql删除重复数据
| 阿里云国内75折 回扣 微信号：monov8 |
| 阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6 |
开发时，经常会有清理数据库中重复数据的需求，比如下面这张表report_apply ：
我们需要删除report_name重复的数据，具体步骤如下：
-- 重复数据
SELECT report_name from report_apply GROUP BY report_name HAVING count(report_name) > 1
-- 重复数据中最小的id留下
SELECT MIN(id) from report_apply GROUP BY report_name HAVING count(report_name) > 1
-- 查询所有重复数据并排除最小的id 此为需要删除的数据
SELECT id from report_apply where report_name in (
SELECT report_name from report_apply GROUP BY report_name HAVING count(report_name) > 1
)
and id NOT in(
SELECT MIN(id) from report_apply GROUP BY report_name HAVING count(report_name) > 1
)
-- 直接delete from删除会报错，所以包一层，当做一张临时表
SELECT id from (
SELECT id from report_apply where report_name in (
SELECT report_name from report_apply GROUP BY report_name HAVING count(report_name) > 1
)
and id NOT in(
SELECT MIN(id) from report_apply GROUP BY report_name HAVING count(report_name) > 1
)
) as result
-- 然后delete删除即可
DELETE from report_apply where id in (
SELECT id from (
SELECT id from report_apply where report_name in (
SELECT report_name from report_apply GROUP BY report_name HAVING count(report_name) > 1
)
and id NOT in(
SELECT MIN(id) from report_apply GROUP BY report_name HAVING count(report_name) > 1
)
) as result
)
You can't specify target table 'report_apply ' for update in FROM clause
这个错误就是因为，外面没有包一层，加一层当做一张新表就可以了。
| 阿里云国内75折 回扣 微信号：monov8 |
| 阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6 |
