實際開發(fā)中遇到一個問題，線上系統(tǒng)報錯，需要手動向庫里插入數(shù)據(jù)，然后點了兩次手動發(fā)送按鈕，導(dǎo)致庫中有重復(fù)的數(shù)據(jù)。需求：根據(jù)某個字段查詢重復(fù)數(shù)據(jù)并刪除

1.查詢出所有數(shù)據(jù)進行分組之后，和重復(fù)數(shù)據(jù)的重復(fù)次數(shù)的查詢數(shù)據(jù)，先列下：

select * from table1 t1 where (t1.username) in  (select username from table1 group by username  having count(*) > 1)

2.刪除重復(fù)數(shù)據(jù)并保留id最小的數(shù)據(jù)(以下是搜出來的)

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)

但是執(zhí)行會報錯，

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
)