sql-- 找到重複資料并删除、有重複資料不插入或更新的處理方法

表結構:

需求:找到相同的内容并删除

方法1:

通過分組找出篩選出count大于1的資料

select r_content, r_userid, count(*) from reviews GROUP BY r_content, r_userid having count(*) > 1      

 group by可以利用聚合函數(count、 sum、 avg)進行分組

having 對分組的資料進行下一步篩選

 通過inner join找出相同資料

select *
from reviews a
         INNER JOIN
     (select r_content, r_userid, count(*) from reviews GROUP BY r_content, r_userid having count(*) > 1) b
     on a.r_content = b.r_content and a.r_userid = b.r_userid;      

加行号

select a.*,
       if(@tmp = CONCAT(r_content, r_userid), @rownum := @rownum + 1, @rownum := 1) as rownum,
       @tmp := CONCAT(r_content, r_userid)
from (
         select a.*
         from reviews a
                  INNER JOIN
              (select r_content, r_userid, count(*) from reviews GROUP BY r_content, r_userid having count(*) > 1) b
              on a.r_content = b.r_content and a.r_userid = b.r_userid) a,
     (select @rownum := 0, @tmp := \'\') b      

取出行号大于的删除之

delete from reviews where r_id in(
select r_id from (
select a.*,
       if(@tmp = CONCAT(r_content, r_userid), @rownum := @rownum + 1, @rownum := 1) as rownum,
       @tmp := CONCAT(r_content, r_userid)
from (
         select a.*
         from reviews a
                  INNER JOIN
              (select r_content, r_userid, count(*) from reviews GROUP BY r_content, r_userid having count(*) > 1) b
              on a.r_content = b.r_content and a.r_userid = b.r_userid) a,
     (select @rownum := 0, @tmp := \'\') b) a where rownum>1);      

方法2:

通過group_concat來組合id,并删除

select group_concat(ids separator \'|\') as ids from(
select group_concat(r_id) as ids ,r_content,r_userid,count(*) from  reviews  group by r_content,r_userid having count(*)>1) a      

有重複資料不插入或更新的處理方法

表設計:

需求:如果有相同内容則不插入,記錄插入相同資料的條數

insert  into news (news_title, news_abstract,news_code) values (\'這是一條新聞\',\'新聞内容是XXX\',md5(concat(\'這是一條新聞\',\'新聞内容是XXX\'))) on duplicate key update dumpnum=dumpnum+1;      

1. 

2.