±ÈÈçÏÖÔÚÓÐÒ»ÈËÔ±±í £¨±íÃû£ºpeosons£© ÈôÏ뽫ÐÕÃû¡¢Éí·ÝÖ¤ºÅ¡¢×¡Ö·ÕâÈý¸ö×Ö¶ÎÍêÈ«ÏàͬµÄ¼Ç¼²éѯ³öÀ´ select p1.* from persons p1,persons p2 where p1.id<>p2.id and p1.cardid = p2.cardid and p1.pname = p2.pname and p1.address = p2.address ¿ÉÒÔʵÏÖÉÏÊöЧ¹û£® ¼¸¸öɾ³ýÖظ´¼Ç¼µÄSQLÓï¾ä 1.ÓÃrowid·½·¨ 2.ÓÃgroup by·½·¨ 3.ÓÃdistinct·½·¨ 1¡£ÓÃrowid·½·¨ ¾Ý¾Ýoracle´øµÄrowidÊôÐÔ£¬½øÐÐÅжϣ¬ÊÇ·ñ´æÔÚÖظ´,Óï¾äÈçÏ£º ²éÊý¾Ý: select * from table1 a where rowid !=(select max(rowid) from table1 b where a.name1=b.name1 and a.name2=b.name2......) ɾÊý¾Ý£º delete from table1 a where rowid !=(select max(rowid) from table1 b where a.name1=b.name1 and a.name2=b.name2......) 2.group by·½·¨ ²éÊý¾Ý: ¡¡¡¡select count(num), max(name) from student --ÁгöÖظ´µÄ¼Ç¼Êý£¬²¢ÁгöËûµÄnameÊôÐÔ ¡¡¡¡group by num ¡¡¡¡having count(num) >1 --°´num·Ö×éºóÕÒ³ö±íÖÐnumÁÐÖظ´£¬¼´³öÏÖ´ÎÊý´óÓÚÒ»´Î ɾÊý¾Ý£º ¡¡¡¡delete from student ¡¡¡¡group by num ¡¡¡¡having count(num) >1 ¡¡¡¡ÕâÑùµÄ»°¾Í°ÑËùÓÐÖظ´µÄ¶¼É¾³ýÁË¡£ 3.ÓÃdistinct·½·¨ -¶ÔÓÚСµÄ±í±È½ÏÓÐÓà create table table_new as select distinct * from table1 minux truncate table table1; insert into table1 select * from table_new; ²éѯ¼°É¾³ýÖظ´¼Ç¼µÄ·½·¨´óÈ« 1¡¢²éÕÒ±íÖжàÓàµÄÖظ´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö×ֶΣ¨peopleId£©À´ÅÐ¶Ï select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) 2¡¢É¾³ý±íÖжàÓàµÄÖظ´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö×ֶΣ¨peopleId£©À´Åжϣ¬Ö»ÁôÓÐrowid×îСµÄ¼Ç¼ delete from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1) 3¡¢²éÕÒ±íÖжàÓàµÄÖظ´¼Ç¼£¨¶à¸ö×ֶΣ© select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) 4¡¢É¾³ý±íÖжàÓàµÄÖظ´¼Ç¼£¨¶à¸ö×ֶΣ©£¬Ö»ÁôÓÐrowid×îСµÄ¼Ç¼ delete from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) 5¡¢²éÕÒ±íÖжàÓàµÄÖظ´¼Ç¼£¨¶à¸ö×ֶΣ©£¬²»°üº¬rowid×îСµÄ¼Ç¼ select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) (¶þ) ±È·½Ëµ ÔÚA±íÖдæÔÚÒ»¸ö×ֶΡ°name¡±£¬ ¶øÇÒ²»Í¬¼Ç¼֮¼äµÄ¡°name¡±ÖµÓпÉÄÜ»áÏàͬ£¬ ÏÖÔÚ¾ÍÊÇÐèÒª²éѯ³öÔڸñíÖеĸ÷¼Ç¼֮¼ä£¬¡°name¡±Öµ´æÔÚÖظ´µÄÏ Select Name,Count(*) From A Group By Name Having Count(*) > 1 Èç¹û»¹²éÐÔ±ðÒ²Ïàͬ´óÔòÈçÏÂ: Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1 (Èý) ·½·¨Ò» declare @max integer,@id integer declare cur_rows cursor local for select Ö÷×Ö¶Î,count(*) from ±íÃû group by Ö÷×ֶΠhaving count(*) >£» 1 open cur_rows fetch cur_rows into @id,@max while @@fetch_status=0 begin select @max = @max -1 set rowcount @max delete from ±íÃû where Ö÷×ֶΠ= @id fetch cur_rows into @id,@max end close cur_rows set rowcount 0 ·½·¨¶þ £¢Öظ´¼Ç¼£¢ÓÐÁ½¸öÒâÒåÉϵÄÖظ´¼Ç¼£¬Ò»ÊÇÍêÈ«Öظ´µÄ¼Ç¼£¬Ò²¼´ËùÓÐ×ֶξùÖظ´µÄ¼Ç¼£¬¶þÊDz¿·Ö¹Ø¼ü×Ö¶ÎÖظ´µÄ¼Ç¼£¬ ±ÈÈçName×Ö¶ÎÖظ´£¬¶øÆäËû×ֶβ»Ò»¶¨Öظ´»ò¶¼Öظ´¿ÉÒÔºöÂÔ¡£ 1¡¢¶ÔÓÚµÚÒ»ÖÖÖظ´£¬±È½ÏÈÝÒ×½â¾ö£¬Ê¹Óà select distinct * from tableName¾Í¿ÉÒԵõ½ÎÞÖظ´¼Ç¼µÄ½á¹û¼¯¡£ Èç¹û¸Ã±íÐèҪɾ³ýÖظ´µÄ¼Ç¼£¨Öظ´¼Ç¼±£Áô1Ìõ£©£¬¿ÉÒÔ°´ÒÔÏ·½·¨É¾³ý select distinct * into #Tmp from tableName drop table tableName select * into tableName from #Tmp drop table #Tmp ·¢ÉúÕâÖÖÖظ´µÄÔ­ÒòÊDZíÉè¼Æ²»ÖܲúÉúµÄ£¬Ôö¼ÓΨһË÷ÒýÁм´¿É½â¾ö¡£ 2¡¢ÕâÀàÖظ´ÎÊÌâͨ³£ÒªÇó±£ÁôÖظ´¼Ç¼ÖеĵÚÒ»Ìõ¼Ç¼£¬²Ù×÷·½·¨ÈçÏ ¼ÙÉèÓÐÖظ´µÄ×Ö¶ÎΪName,Address£¬ÒªÇóµÃµ½ÕâÁ½¸ö×Ö¶ÎΨһµÄ½á¹û¼¯ select identity(int,1,1) as autoID, * into #Tmp from tableName select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID select * from #Tmp where autoID in(select autoID from #tmp2) ¡¡¡¡×îºóÒ»¸öselect¼´µÃµ½ÁËName£¬Address²»Öظ´µÄ½á¹û¼¯£¨µ«¶àÁËÒ»¸öautoID×ֶΣ¬Êµ¼Êдʱ¿ÉÒÔдÔÚselect×Ó¾äÖÐÊ¡È¥´ËÁУ© (ËÄ) ²éѯÖظ´ select * from tablename where id in ( select id from tablename group by id having count(id) > 1 ) Àý×Ó delete from w_m_mjout where ksid in (select ksid from tableName group by ksid HAVING COUNT(ksid)>1) and rowid not in (select min(ROWID) from tableName group by ksid HAVING COUNT(ksid)>1)