sql查找相同数据与删除相同记录中的一行

1. 查找数据表中某些字段相同的数据，查找相同数据：小技巧1：select * from tablename tn where((select count(*) from tablename where column1= tn.column1 and column2=tn.column2 ) > 1)提示：可以接order by 语句更方便查看

2.删除相同记录中的一行：小技巧1：当表中存在唯一键值的情况：delete from tablename where id not in(select max(id) from tablename group by column1,column2)小技巧2：不存在唯一键值的情况，最简单的方法当然是新增一个列id，然后用1中的方法了。不过可以用中间表存取的方式：首选建一个和a表一样结构的中间表，然后insert into b select distinct * from a，再删除表a，delete from a，最后把b中的数据插入到a中， insert into a select * from b

例如:表caicai ,字段id,title,content

 

select * from caicai where title in ( select title from caicai group by title having count(*) >1)

 

select * from caicai tn where((select count(*) from caicai where title= tn.title) > 1)

 

delete from caicai where id not in(select max(id) from caicai group by title)

 

SELECT infoid, title, brwsPath, indexed, indexed2 FROM infos WHERE (title IN(SELECT titleFROM infosGROUP BY titleHAVING COUNT(title) > 1)) ORDER BY title DESC