ÆäËü»¥ÁªÍøÕª³£º
1¡¢Ð´Ò»ÕűíÖÐÓÐidºÍname Á½¸ö×ֶΣ¬²éѯ³önameÖØ¸´µÄËùÓÐÊý¾Ý£¬ÏÖÔÚÁÐÏ£º
select * from xi a where (a.username) in  (select username from xi group by username  having count(*) > 1)
2¡¢²éѯ³öËùÓÐÊý¾Ý½øÐзÖ×éÖ®ºó£¬ºÍÖØ¸´Êý¾ÝµÄÖØ¸´´ÎÊýµÄ²éѯÊý¾Ý£¬ÏÈÁÐÏ£º
select  count(username) as ¡¯Öظ´´ÎÊý¡¯,username from xi group by username  having count(*)>1 order by username desc
3¡¢Ò»ÏÂΪ ²é¿´±ðÈ˵Ġ½á¹û£¬ÏÖÁÐÏ£º²éѯ¼°É¾³ýÖØ¸´¼Ç¼µÄ·½·¨´óÈ«
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 integerdeclare cur_rows cursor local for select Ö÷×Ö¶Î,count(*) from ±íÃû group by Ö÷×ֶΠhaving count(*) >£» 1open cur_rowsfetch cur_rows into @id,@maxwhile @@fetch_status=0beginselect @max = @max -1set rowcount @maxdelete from ±íÃû where Ö÷×ֶΠ= @idfetch cur_rows into @id,@maxendclose cur_rowsset rowcount 0
·½·¨¶þ£¢Öظ´¼Ç¼£¢ÓÐÁ½¸öÒâÒåÉϵÄÖØ¸´¼Ç¼£¬Ò»ÊÇÍêÈ«ÖØ¸´µÄ¼Ç¼£¬Ò²¼´ËùÓÐ×ֶξùÖØ¸´µÄ¼Ç¼£¬¶þÊDz¿·Ö¹Ø¼ü×Ö¶ÎÖØ¸´µÄ¼Ç¼£¬±ÈÈçName×Ö¶ÎÖØ¸´£¬¶øÆäËû×ֶβ»Ò»¶¨Öظ´»ò¶¼Öظ´¿ÉÒÔºöÂÔ¡£
¡¡¡¡1¡¢¶ÔÓÚµÚÒ»ÖÖÖØ¸´£¬±È½ÏÈÝÒ×½â¾ö£¬Ê¹ÓÃselect distinct * from tableName¡¡¡¡¾Í¿ÉÒԵõ½ÎÞÖØ¸´¼Ç¼µÄ½á¹û¼¯¡£¡¡¡¡Èç¹û¸Ã±íÐèҪɾ³ýÖØ¸´µÄ¼Ç¼£¨Öظ´¼Ç¼±£Áô1Ìõ£©£¬¿ÉÒÔ°´ÒÔÏ·½·¨É¾³ýselect distinct * into #Tmp from tableNamedrop table tableNameselect * into tableName from #Tmpdrop table #Tmp¡¡¡¡·¢ÉúÕâÖÖÖØ¸´µÄÔÒòÊDZíÉè¼Æ²»ÖܲúÉúµÄ£¬Ôö¼ÓΨһË÷ÒýÁм´¿É½â¾ö¡£
¡¡¡¡2¡¢ÕâÀàÖØ¸´ÎÊÌâͨ³£ÒªÇó±£ÁôÖØ¸´¼Ç¼ÖеĵÚÒ»Ìõ¼Ç¼£¬²Ù×÷·½·¨ÈçÏ¡¡¡¡¼ÙÉèÓÐÖØ¸´µÄ×Ö¶ÎΪName,Address£¬ÒªÇóµÃµ½ÕâÁ½¸ö×Ö¶ÎΨһµÄ½á¹û¼¯select identity(int,1,1) as autoID, * into #Tmp from tableNameselect min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoIDselect * from #Tmp where autoID in(select autoID from #tmp2)¡¡¡¡×îºóÒ»¸öselect¼´µÃµ½ÁËName£¬Address²»Öظ´µÄ½á¹û¼¯£¨µ«¶àÁËÒ»¸öautoID×ֶΣ¬Êµ¼Êдʱ¿ÉÒÔдÔÚselect×Ó¾äÖÐʡȥ´ËÁУ©
(ËÄ)
²éÑ¯ÖØ¸´select * from tablename where id in (select id from tablenamegroup by idhaving count(id) > 1)
*******¾ÙÀý*********
²éѯ Sap Business OneÖÆÔìÉÌOMRC±íÖÐÖØ¸´Êý¾Ý(Pass)
select * from omrc
where firmname in (select  firmname  from  omrc  group  by  firmname  having  count(firmname) > 1)
ɾ³ý Sap Business OneÖÆÔìÉÌOMRC±íÖÐÖØ¸´Êý¾Ý(Pass)
delete from omrc
where FirmName  in (select  FirmName  from omrc  group  by  FirmName   having  count(FirmName) > 1)
and FirmCode not in (select min(FirmCode) from  omrc  group by FirmName  having count(FirmName )>1)
OMRCΪ±íÃû£¬FirmName/FirmCodeΪ×Ö¶ÎÃû¡£
***ɾ³ý»ò±£ÁôһЩÐÐÊý¾Ý
Êý¾Ý¿âint001¼¸¸ö±íOMRC/POR1/RDR1£¬±£ÁôǰÁùÐÐÊý¾Ý(²Ù×÷£¬³öÏÖ±í´íÎ󣬴¦Àíºó/truncate/ÔÙµ¼Èë)
DELETE OMRC WHERE FirmCode NOT IN(SELECT TOP 6 FirmCode FROM OMRC)
DELETE Por1 WHERE DocEntry NOT IN(SELECT TOP 6 DocEntry FROM Por1)
DELETE RDR1 WHERE DocEntry NOT IN(SELECT TOP 6 DocEntry FROM RDR1)
 
*****************************
²éѯÊý¾Ý¿âÖдøÓÐij¸ö×ֶεÄËùÓбíÃû
MySQLÊý¾Ý¿â²éѯ´øÓÐij¸ö×ֶεÄËùÓбíÃû£º
£¨1£©¾«È·²éѯÓï¾äÈçÏ£º
SELECT * FROM information_schema.columns WHERE column_name=¡¯column_name¡¯
£¨2£©Ä£ºýÆ¥Åä²éѯ
SELECT * FROM information_schema.columns WHERE column_name LIKE ¡¯%column_name%¡¯
OracleÊý¾Ý¿â²éѯ´øÓÐij¸ö×ֶεÄËùÓбíÃû£º
£¨1£©¾«È·²éѯÓï¾äÈçÏ£º
SELECT column_name,table_name FROM user_tab_columns WHERE column_name=¡¯column_name¡¯
£¨2£©Ä£ºýÆ¥Åä²éѯ
SELECT column_name,table_name,FROM user_tab_columns WHERE column_name LIKE ¡¯%column_name%¡¯
SQLServerÊý¾Ý¿â²éѯ´øÓÐij¸ö×ֶεÄËùÓбíÃû£º
£¨1£©¾«È·²éѯÓï¾äÈçÏ£º
SELECT [name] FROM [¿âÃû].[dbo].sysobjects WHERE id IN (SELECT id FROM [¿âÃû].[dbo].syscolumns WHERE name = ¡¯×Ö¶ÎÃû¡¯)
£¨2£©Ä£ºýÆ¥Åä²éѯ
SELECT [name] FROM [¿âÃû].[dbo].sysobjects WHERE id IN (SELECT id FROM [¿âÃû].[dbo].syscolumns WHERE name LIKE ¡¯%×Ö¶ÎÃû%¡¯)