【SQL进阶】找到重复数据、并删除

 

reviews 字段说明

1、r_id  自增ID

2、r_content  评论内容

3、r_userid  评论人id

4、news_id 对应的新闻id

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 
		) c where c.rownum>1
)