mysql删除重复记录并且只保留一条

Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1

count(*)与count（列名）的区别：

    count(*)将返回表格中所有存在的行的总数包括值为null的行，然而count(列名)将返回表格中除去null以外的所有行的总数(有默认值的列也会被计入）

 

    
delete from logisticscodecheckout_copy1 ta
where exists
(
select tb.Id from logisticscodecheckout_copy1 tb
where ta.LogisticsCode = tb.LogisticsCode
and ta.Id<tb.Id 
)        
and ta.LogisticsCode='003000199344'

## You can't specify target table 'logisticscodecheckout_copy1' for update in FROM clause

delete from tbl where id in
(
    select a.id from
    (
        select max(id) id from tbl a where EXISTS
        (
            select 1 from tbl b where a.tac=b.tac group by tac HAVING count(1)>1
        )
        group by tac
    ) a
)

 
--执行成功
delete from logisticscodecheckout_copy1 where Id in
(
    select a.Id from
    (
        select ta.Id from logisticscodecheckout_copy1 ta
where exists
(
select tb.Id from logisticscodecheckout_copy1 tb
where ta.LogisticsCode = tb.LogisticsCode
and ta.Id<tb.Id 
)        
    ) a
)

 

  

 

 

DELETE
FROM
    dept
WHERE
    deptno NOT IN (
        SELECT
            dt.minno
        FROM
            (
                SELECT
                    MIN(deptno) AS minno
                FROM
                    dept
                GROUP BY
                    dname
            ) dt
    )

  

来源 :

https://www.jb51.net/article/60926.htm

https://blog.csdn.net/n950814abc/article/details/82284838 

 

SQL 删除重复记录方法：https://www.cnblogs.com/shy1766IT/p/5185719.html