此时情绪此时天,无事小神仙
好好生活,平平淡淡每一天

编辑

SQL去重复数据

1、查找表中重复数据,重复数据以单个字段(title)为标识

select id,title from t_article 
where title in (
  select title from (
    select title as title from t_article 
    group by title having count(title) > 1
  ) as title
)
order by title

2、删除表中多余的重复数据,重复数据以单个字段(title)为标识,只留有id最小的数据

delete from t_article 
where title in (
  select title from (
    select title as title from t_article 
    group by title having count(title) > 1
  ) as title
)
and id not in (
  select id from (
    select min(id) as id from t_article t group by title having count(title) > 1
  ) as id
);

3、查找表中重复数据,重复数据以(title,source)多个字段为标识

select id,title,source,upload_time from t_article 
where (title,source) in (
  select title,source from t_article 
  group by title,source having count(*) > 1
)
order by title

4、删除表中多余的重复数据,重复数据以(title,source)多个字段为标识,只留有id最小的数据

delete from t_article 
where id in (
    select id from (
        select id from t_article 
        where (title,source) in (
            select title,source from t_article 
            group by title,source having count(*) > 1
        )
        and id not in (
            select id from (
                select min(id) as id from t_article 
                group by title,source having count(*) > 1
            ) as id
        )
    )as id    
) 
posted @ 2021-01-14 16:14  踏步  阅读(254)  评论(4编辑  收藏  举报