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