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

时间:2022-06-19 19:17:33

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