ºìÁªLinuxÃÅ»§
Linux°ïÖú

Oracle ÖÐÈçºÎɾ³ýÖظ´Êý¾Ý

·¢²¼Ê±¼ä:2006-01-19 09:58:04À´Ô´:ºìÁª×÷Õß:zz123
ÎÒÃÇ¿ÉÄÜ»á³öÏÖÕâÖÖÇé¿ö£¬Ä³¸ö±íÔ­À´Éè¼Æ²»ÖÜÈ«£¬µ¼Ö±íÀïÃæµÄÊý¾ÝÊý¾ÝÖظ´£¬ÄÇô£¬ÈçºÎ¶ÔÖظ´µÄÊý¾Ý½øÐÐɾ³ýÄØ£¿
Öظ´µÄÊý¾Ý¿ÉÄÜÓÐÕâÑùÁ½ÖÖÇé¿ö£¬µÚÒ»ÖÖʱ±íÖÐÖ»ÓÐijЩ×Ö¶ÎÒ»Ñù£¬µÚ¶þÖÖÊÇÁ½ÐмǼÍêÈ«Ò»Ñù¡£

Ò»¡¢¶ÔÓÚ²¿·Ö×Ö¶ÎÖظ´Êý¾ÝµÄɾ³ý
ÏÈÀ´Ì¸Ì¸ÈçºÎ²éѯÖظ´µÄÊý¾Ý°É¡£
ÏÂÃæÓï¾ä¿ÉÒÔ²éѯ³öÄÇЩÊý¾ÝÊÇÖظ´µÄ£º
select ×Ö¶Î1,×Ö¶Î2,count(*) from ±íÃû group by ×Ö¶Î1,×Ö¶Î2 having count(*) > 1
½«ÉÏÃæµÄ>ºÅ¸ÄΪ=ºÅ¾Í¿ÉÒÔ²éѯ³öûÓÐÖظ´µÄÊý¾ÝÁË¡£
ÏëҪɾ³ýÕâЩÖظ´µÄÊý¾Ý£¬¿ÉÒÔʹÓÃÏÂÃæÓï¾ä½øÐÐɾ³ý
delete from ±íÃû a where ×Ö¶Î1,×Ö¶Î2 in
(select ×Ö¶Î1,×Ö¶Î2,count(*) from ±íÃû group by ×Ö¶Î1,×Ö¶Î2 having count(*) > 1)
ÉÏÃæµÄÓï¾ä·Ç³£¼òµ¥£¬¾ÍÊǽ«²éѯµ½µÄÊý¾Ýɾ³ýµô¡£²»¹ýÕâÖÖɾ³ýÖ´ÐеÄЧÂʷdz£µÍ£¬¶ÔÓÚ´óÊý¾ÝÁ¿À´Ëµ£¬¿ÉÄܻὫÊý¾Ý¿âµõËÀ¡£ËùÒÔÎÒ½¨ÒéÏȽ«²éѯµ½µÄÖظ´µÄÊý¾Ý²åÈëµ½Ò»¸öÁÙʱ±íÖУ¬È»ºó¶Ô½øÐÐɾ³ý£¬ÕâÑù£¬Ö´ÐÐɾ³ýµÄʱºò¾Í²»ÓÃÔÙ½øÐÐÒ»´Î²éѯÁË¡£ÈçÏ£º
CREATE TABLE ÁÙʱ±í AS
(select ×Ö¶Î1,×Ö¶Î2,count(*) from ±íÃû group by ×Ö¶Î1,×Ö¶Î2 having count(*) > 1)
ÉÏÃæÕâ¾ä»°¾ÍÊǽ¨Á¢ÁËÁÙʱ±í£¬²¢½«²éѯµ½µÄÊý¾Ý²åÈëÆäÖС£
ÏÂÃæ¾Í¿ÉÒÔ½øÐÐÕâÑùµÄɾ³ý²Ù×÷ÁË£º
delete from ±íÃû a where ×Ö¶Î1,×Ö¶Î2 in (select ×Ö¶Î1£¬×Ö¶Î2 from ÁÙʱ±í);
ÕâÖÖÏȽ¨ÁÙʱ±íÔÙ½øÐÐɾ³ýµÄ²Ù×÷Òª±ÈÖ±½ÓÓÃÒ»ÌõÓï¾ä½øÐÐɾ³ýÒª¸ßЧµÃ¶à¡£

Õâ¸öʱºò£¬´ó¼Ò¿ÉÄÜ»áÌø³öÀ´Ëµ£¬Ê²Ã´£¿Äã½ÐÎÒÃÇÖ´ÐÐÕâÖÖÓï¾ä£¬ÄDz»ÊÇ°ÑËùÓÐÖظ´µÄÈ«¶¼É¾³ýÂ𣿶øÎÒÃÇÏë±£ÁôÖظ´Êý¾ÝÖÐ×îеÄÒ»Ìõ¼Ç¼°¡£¡´ó¼Ò²»Òª¼±£¬ÏÂÃæÎҾͽ²Ò»ÏÂÈçºÎ½øÐÐÕâÖÖ²Ù×÷¡£
ÔÚoracleÖУ¬ÓиöÒþ²ØÁË×Ô¶¯rowid£¬ÀïÃæ¸øÿÌõ¼Ç¼һ¸öΨһµÄrowid£¬ÎÒÃÇÈç¹ûÏë±£Áô×îеÄÒ»Ìõ¼Ç¼£¬
ÎÒÃǾͿÉÒÔÀûÓÃÕâ¸ö×ֶΣ¬±£ÁôÖظ´Êý¾ÝÖÐrowid×î´óµÄÒ»Ìõ¼Ç¼¾Í¿ÉÒÔÁË¡£
ÏÂÃæÊDzéѯÖظ´Êý¾ÝµÄÒ»¸öÀý×Ó£º
select a.rowid,a.* from ±íÃû a
where a.rowid !=
(
select max(b.rowid) from ±íÃû b
where a.×Ö¶Î1 = b.×Ö¶Î1 and
a.×Ö¶Î2 = b.×Ö¶Î2
)
ÏÂÃæÎÒ¾ÍÀ´½²½âһϣ¬ÉÏÃæÀ¨ºÅÖеÄÓï¾äÊDzéѯ³öÖظ´Êý¾ÝÖÐrowid×î´óµÄÒ»Ìõ¼Ç¼¡£
¶øÍâÃæ¾ÍÊDzéѯ³ö³ýÁËrowid×î´óÖ®ÍâµÄÆäËûÖظ´µÄÊý¾ÝÁË¡£
ÓÉ´Ë£¬ÎÒÃÇҪɾ³ýÖظ´Êý¾Ý£¬Ö»±£Áô×îеÄÒ»ÌõÊý¾Ý£¬¾Í¿ÉÒÔÕâÑùдÁË£º
delete from ±íÃû a
where a.rowid !=
(
select max(b.rowid) from ±íÃû b
where a.×Ö¶Î1 = b.×Ö¶Î1 and
a.×Ö¶Î2 = b.×Ö¶Î2
)

Ëæ±ã˵һÏ£¬ÉÏÃæÓï¾äµÄÖ´ÐÐЧÂÊÊǺܵ͵ģ¬¿ÉÒÔ¿¼Âǽ¨Á¢ÁÙʱ±í£¬½²ÐèÒªÅжÏÖظ´µÄ×ֶΡ¢rowid²åÈëÁÙʱ±íÖУ¬È»ºóɾ³ýµÄʱºòÔÚ½øÐбȽϡ£
create table ÁÙʱ±í as
select a.×Ö¶Î1,a.×Ö¶Î2,MAX(a.ROWID) dataid from Õýʽ±í a GROUP BY a.×Ö¶Î1,a.×Ö¶Î2;
delete from ±íÃû a
where a.rowid !=
(
select b.dataid from ÁÙʱ±í b
where a.×Ö¶Î1 = b.×Ö¶Î1 and
a.×Ö¶Î2 = b.×Ö¶Î2
);
commit;

¶þ¡¢¶ÔÓÚÍêÈ«Öظ´¼Ç¼µÄɾ³ý

¶ÔÓÚ±íÖÐÁ½ÐмǼÍêÈ«Ò»ÑùµÄÇé¿ö£¬¿ÉÒÔÓÃÏÂÃæÓï¾ä»ñÈ¡µ½È¥µôÖظ´Êý¾ÝºóµÄ¼Ç¼£º
select distinct * from ±íÃû
¿ÉÒÔ½«²éѯµÄ¼Ç¼·Åµ½ÁÙʱ±íÖУ¬È»ºóÔÙ½«Ô­À´µÄ±í¼Ç¼ɾ³ý£¬×îºó½«ÁÙʱ±íµÄÊý¾Ýµ¼»ØÔ­À´µÄ±íÖС£ÈçÏ£º
CREATE TABLE ÁÙʱ±í AS (select distinct * from ±íÃû);
drop table Õýʽ±í;
insert into Õýʽ±í (select * from ÁÙʱ±í);
drop table ÁÙʱ±í;

Èç¹ûÏëɾ³ýÒ»¸ö±íµÄÖظ´Êý¾Ý£¬¿ÉÒÔÏȽ¨Ò»¸öÁÙʱ±í£¬½«È¥µôÖظ´Êý¾ÝºóµÄÊý¾Ýµ¼Èëµ½ÁÙʱ±í£¬È»ºóÔÚ´Ó
ÁÙʱ±í½«Êý¾Ýµ¼ÈëÕýʽ±íÖУ¬ÈçÏ£º
INSERT INTO t_table_bak
select distinct * from t_table;
ÎÄÕÂÆÀÂÛ

¹²ÓÐ 1 ÌõÆÀÂÛ

  1. dongyichen ÓÚ 2011-08-05 08:51:09·¢±í:

    лл~