实际开发中遇到一个问题，线上系统报错，需要手动向库里插入数据，然后点了两次手动发送按钮，导致库中有重复的数据。需求：根据某个字段查询重复数据并删除
1.查询出所有数据进行分组之后，和重复数据的重复次数的查询数据，先列下：
select * from table1 t1 where (t1.username) in (select username from table1 group by username having count(*) > 1)
2.删除重复数据并保留id最小的数据(以下是搜出来的)
delete from people
where peopleId in (select peopleId from people group by username having count(username) > 1)
and rowid not in (select min(rowid) from people group by username having count(username )>1)
但是执行会报错，
You can't specify target table for update in FROM clause
意思是说mysql中You can't specify target table <tbl> for update in FROM clause错误的意思是说，不能先select出同一表中的某些值，再update这个表(在同一语句中)。
这是需要一个中间临时表，将删除改成以下
delete from
people
where
peopleId in
(select peopleId
from
(select id from people
where
peopleId in (select peopleId from people group by username having count(username)>1)
and peopleId not in(select min(peopleId) from people group by username having count(username)>1)
) as tmpresult
)
多字段属性删除
DELETE
FROM
user_organization_access a
WHERE
(a.user_id, a.organization_id) IN (
select user_id,
organization_id from (
SELECT
user_id,
organization_id
FROM
user_organization_access
GROUP BY
user_id,
organization_id
HAVING
count(*) > 1)t1
)
AND organization_login_id NOT IN (
select * from (
SELECT
min(organization_login_id)
FROM
user_organization_access
GROUP BY
user_id,
organization_id
HAVING
count(*) > 1) t
)
