¼¸¸öɾ³ýÖظ´¼Ç¼µÄSQLÓï¾ä

¼¸¸öɾ³ýÖظ´¼Ç¼µÄSQLÓï¾ä
2009-03-02 10:08
±ÈÈçÏÖÔÚÓÐÒ»ÈËÔ±±í £¨±íÃû£º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 peop