oracle中如何查询数据表中重复的数据？ oracle怎样查出表中重复列的数据？
oracle\u4e2d\u5982\u4f55\u67e5\u8be2\u6570\u636e\u8868\u4e2d\u91cd\u590d\u7684\u6570\u636e\uff1f\u5982\u679c\u60f3\u67e5\u8be2\u6570\u636e\u8868\u4e2d\u67d0\u4e00\u4e2a\u5b57\u6bb5\u91cd\u590d\uff08\u8fd9\u91cc\u5047\u8bbe\u8fd9\u4e2a\u5b57\u6bb5\u540d\u662fID1\uff09\uff0c\u53ef\u4ee5\u4f7f\u7528\u4ee5\u4e0bSQL\u8bed\u53e5\u3002
select Table1.* from Table1 right join (
select ID1 From Table1 Group by ID1 having Count(ID1) > 1 ) T on Table1.id1 = T.id1
\u5982\u679c\u60f3\u67e5\u8be2\u6570\u636e\u8868\u67d0\u4e24\u4e2a\u5b57\u6bb5\u91cd\u590d\uff0c\u5219\u53ef\u4ee5\u4f7f\u7528\u5982\u4e0b\u8bed\u53e5\u67e5\u8be2\u3002
select Table1.*
from Table1 right join (
select ID1, ID2 From Table1 Group by ID1, ID2 having Count(ID1) > 1 and Count(ID2) > 1 ) T
1\u3001\u67e5\u51fa\u8868\u4e2d\u91cd\u590d\u5217\u7684\u6570\u636e\uff1aselect a,count(*) from table group by a having count(*)>1
2\u3001\u67e5\u91cd\u590d\u6b21\u6570\u6700\u591a\u7684\u5217\uff1aselect a,num from (select a,count(*) num from table group by a having count(*)>1)order by num desc
\u6b64\u5916\uff0c\u8fd8\u6709
1\u3001\u67e5\u8be2\u4e00\u4e2a\u8868\u4e2d\u6240\u6709\u5b57\u6bb5\u90fd\u76f8\u540c\u7684\u8bb0\u5f55
\u6bd4\u5982\u73b0\u5728\u6709\u4e00\u4eba\u5458\u8868 \uff08\u8868\u540d\uff1apeosons\uff09\u82e5\u60f3\u5c06\u59d3\u540d\u3001\u7f16\u53f7\u3001\u4f4f\u5740\u8fd9\u4e09\u4e2a\u5b57\u6bb5\u5b8c\u5168\u76f8\u540c\u7684\u8bb0\u5f55\u67e5\u8be2\u51fa\u6765\uff1a
select p1.* from persons p1,persons p2 where p1.name=p2.name and p1.id = p2.id and p1.address=p2.address group by p1.name,p1.id,p1.address having count(*) >1;
\u6216\u8005\uff1a
select p1.* from persons p1,persons p2 where p1.name=p2.name
and p1.id=p2.id and p1.address=p2.address and p1.rowidp2.rowid;
\u6216\u8005\uff1a(\u4e0b\u9762\u8fd9\u6761\u8bed\u53e5\u6267\u884c\u6548\u7387\u66f4\u9ad8)
select * from (select p.*,row_number() over (partition by name,
id,address order by name) rn from persons p) where rn>1;
2\u3001 \u67e5\u8be2\u4e00\u4e2a\u8868\u4e2d\u67d0\u5b57\u6bb5\u76f8\u540c\u7684\u8bb0\u5f55
\u8bed\u6cd5\uff1aselect p1.* from \u8868\u540d p1,(select \u5b57\u6bb5 from \u8868\u540d group by \u5b57\u6bb5 having count(*)>1) p2 where p1.\u5b57\u6bb5=p2.\u5b57\u6bb5;
select p1.* from persons p1,(select address from persons group by address having count(*)>1) p2
where p1.address=p2.address;
3\u3001\u67e5\u8be2\u4e00\u4e2a\u8868\u4e2d\u67d0\u5b57\u6bb5\u76f8\u540c\u7684\u8bb0\u5f55\uff0c\u5176\u5b83\u5b57\u6bb5\u4e0d\u7528\u67e5\u8be2\u51fa\u6765
select name,count(*) from persons group by name having count(*) >1;
可以用分组函数统计，例如在表test中查询id字段重复的数据，查询结果中id是重复的数据值，count(*)是重复的次数。
create table test(id number,name varchar2(20));
insert into test values(1,'a');
insert into test values(1,'b');
insert into test values(1,'c');
insert into test values(2,'d');
insert into test values(2,'e');
commit;
SELECT ID,COUNT(*) FROM TEST GROUP BY ID;
使用in或者exists
但是相对来说，使用in的速度慢，可以尝试使用exist（如果数据多，会更明显的感觉到，数据极少，几乎没差别）
1。使用in
SELECT service, name, note
FROM table01
WHERE service NOT IN (SELECT service FROM table02)
2。使用exists
select service, name, note
from table01
where not exists (select service from table02)
绛旓細鍦∣racle鏁版嵁搴撶鐞嗙郴缁熶腑锛浣犲彲浠ラ氳繃鎵ц鐗瑰畾鐨凷QL鏌ヨ鏉ヨ幏鍙栨墍闇鐨勪俊鎭銆備互涓嬫槸鏌ョ湅鏁版嵁搴撳強鍏惰〃鐨勬柟娉曪細棣栧厛锛岃鏌ョ湅鎵鏈夌郴缁熶腑鍏锋湁鏉冮檺鐨勮〃锛屽彲浠ユ墽琛孲ELECT*FROMALL_TABLES;姝ゅ懡浠や細鍒楀嚭鎵鏈夊彲鐢ㄧ殑琛ㄣ傚叾娆★紝濡傛灉浣犻渶瑕佹煡鐪嬬郴缁熺骇鍒殑琛紝鍙互浣跨敤SELECT*FROMDBA_TABLES;杩欎簺閫氬父鍖呮嫭绠＄悊鏁版嵁搴撶粨鏋勫拰鍏...
绛旓細鍦∣racle鏁版嵁搴撲腑锛鑾峰彇鎵鏈夎〃鍙婂叾瀵瑰簲琛屾暟鐨勬搷浣滃彲浠ラ氳繃绠鍗曠殑SQL鏌ヨ鏉ュ畬鎴銆傚彧闇瑕佸叧娉ㄤ袱涓瓧娈碉紝table_name琛ㄧず琛ㄥ悕锛宯um_rows鍒欐槸琛ㄧ殑琛屾暟銆備互涓嬫槸瀹炵幇杩欎竴鐩爣鐨勪袱绉嶆柟娉曪細棣栧厛锛岃鏌ョ湅鏁版嵁搴撲腑鎵鏈夎〃鐨勮〃鍚嶅拰琛屾暟锛屽彲浠ヤ娇鐢ㄤ互涓嬫煡璇細1銆佹煡璇㈡墍鏈夎〃鐨凷QL璇彞锛歴electt.table_name,t.num_rowsfr...
绛旓細1銆佹湰鏂囦互plsql鍥惧舰鍖栫鐞嗗伐鍏蜂负渚嬶紝浣跨敤瀹冭繛鎺oracle鏁版嵁搴銆傚綋鐐瑰紑plsql鐨勬椂鍊欙紝灏遍渶瑕佽緭鍏ョ敤鎴峰悕銆佸瘑鐮併佹暟鎹簱鐒跺悗杩炴帴銆傚鏋滆繛鎺ユ垚鍔燂紝灏辫兘鏌ョ湅閲岄潰鐨勮〃绛夌瓑鎿嶄綔锛屽鏋滆繛鎺ヤ笉鎴愬姛锛屽氨杈撳叆姝ｇ‘鐨勪俊鎭啀娆″皾璇曘2銆佽繛鎺ヤ笂鏁版嵁搴撲箣鍚庯紝榛樿浼氱湅鍒拌繖涓富鐣岄潰銆傞《閮ㄦ槸鍚勭鎿嶄綔鑿滃崟锛屽乏渚х涓涓鏄渶杩戞墦寮...
绛旓細1銆侀鍏堝湪浣跨敤鐨勭數鑴戜笂锛屾坊鍔犳兂瑕鏌ヨ鏁版嵁搴撶殑鏈嶅姟鍜岀洃鍚紝閫氳繃Oracle瀹㈡埛绔鐞嗗伐鍏蜂腑鐨凬et Manager鏉ュ畬鎴愩2銆佸垱寤轰袱涓暟鎹簱鐨勮繛鎺---DBLink锛屽彲浠ラ氳繃鎸囦护瀹屾垚锛屼篃鍙互閫氳繃鍥惧舰鐣岄潰瀹屾垚銆3銆佸湪Oracle绠＄悊宸ュ叿涓紑鍚竴涓柊鐨凷QL windows绐楀彛锛屾祴璇曟搷浣滄垜浠繛鎺ョ殑寮傚湴Oracle鏁版嵁搴銆4銆佺紪鍐欎竴涓煡璇㈣鍙ユ潵...
绛旓細鏌ヨ璇ュ瓧娈,鐢‥XCPTION寮傚父鎶撳彇锛屽鏋滄病寮傚父鍒欒鏄庢湁璇ュ瓧娈点傚鏋滆繘鍏ュ紓甯哥▼搴忓潡鍒欒鏄庢棤璇ュ瓧娈点侽racle Database锛屽張鍚峅racle RDBMS锛屾垨绠绉癘racle銆傛槸鐢查鏂囧叕鍙哥殑涓娆惧叧绯绘暟鎹簱绠＄悊绯荤粺銆傚畠鏄湪鏁版嵁搴撻鍩熶竴鐩村浜庨鍏堝湴浣嶇殑浜у搧銆傚彲浠ヨOracle鏁版嵁搴绯荤粺鏄洰鍓嶄笘鐣屼笂娴佽鐨勫叧绯绘暟鎹簱绠＄悊绯荤粺锛岀郴缁熷彲绉绘鎬...
绛旓細浠Oracle鏁版嵁搴撲腑鍛戒护鏂规硶鍏ユ墜 閫氳繃鏌ヨ鏁版嵁搴撶郴缁熶腑鐨勬暟鎹瓧鍏歌〃(data dictionary tables)鑾峰彇琛ㄧ┖闂寸殑鐩稿叧淇℃伅 棣栧厛浣跨敤瀹㈡埛绔伐鍏疯繛鎺ュ埌鏁版嵁搴 杩欎簺宸ュ叿鍙互鏄疭QLPLUS瀛楃宸ュ叿 TOAD PL/SQL绛 杩炴帴鍒版暟鎹簱鍚庢墽琛屽涓嬬殑鏌ヨ璇彞 select a a 琛ㄧ┖闂村悕绉 c c 绫诲瀷 c c 鍖虹鐞 b b / / 琛ㄧ┖闂村ぇ灏廙 ...
绛旓細鍦Oracle鏁版嵁搴撲腑锛屾煡鐪嬫墍鏈夎〃鍙婂搴斾釜琛ㄧ殑琛屾暟锛屽彧鐢ㄤ竴涓猻elect璇彞鏌ヨtable_name鍜宯um_rows涓や釜瀛楁鍗冲彲銆倀able_name鏄〃鍚嶏紝num_rows浠ｈ〃琛ㄧ殑琛屾暟銆傚叿浣撳涓嬶細1銆鏌ヨ鏁版嵁搴撴墍鏈夌殑琛╯ql锛歴elect t.table_name,t.num_rows from all_tables t锛泂ql鎵ц鍚庣殑杈撳嚭缁撴灉濡備笅鍥撅細2銆佹煡璇㈠綋鍓嶇敤鎴疯〃sql...
绛旓細鍐欎竴涓瓨鍌ㄨ繃绋媆x0d\x0a灏唖howtables鐨勭粨鏋滄斁鍏ヤ竴涓泦鍚堜腑\x0d\x0a鐒跺悗閬嶅巻杩欎釜闆嗗悎\x0d\x0adesctable\x0d\x0a寰楀埌鍗曚釜琛ㄧ殑瀛楁\x0d\x0a鐒跺悗缁勭粐鏌ヨ璇彞select*fromtablewhere瀛楁1=鈥榗c鈥檕r瀛楁2=鈥檆c鈥...瀛楁n='cc'\x0d\x0a\x0d\x0a杩愯鑴氭湰鐪嬬粨鏋淺x0d\x0a杩欏彧鏄濊矾...
绛旓細1銆鏌ョ湅Oracle鏁版嵁搴撲腑鏁版嵁鏂囦欢淇℃伅鐨勫伐鍏锋柟娉曪細浣跨敤涓婇潰浠嬬粛杩囩殑鏂规硶鐧诲綍oracle enterprise manager console宸ュ叿锛岄夋嫨‘瀛樺偍’ 鏁版嵁鏂囦欢锛屼細鐪嬪埌濡備笅鐨勭晫闈紝璇ョ晫闈㈡樉绀轰簡鏁版嵁鏂囦欢鍚嶇О锛岃〃绌洪棿鍚嶇О锛屼互鍏嗕负鍗曚綅鐨勬暟鎹枃浠跺ぇ灏忥紝宸蹭娇鐢ㄧ殑鏁版嵁鏂囦欢澶у皬鍙婃暟鎹枃浠跺埄鐢ㄧ巼銆
绛旓細1銆侀鍏堥渶瑕佹壘鍒oracle瀹夎鐩綍锛屾墦寮鎺у埗鍙扮鐞嗐2銆佽繘鍏ユ帶鍒跺彴鐣岄潰锛岃緭鍏ョ敤鎴峰悕鍜屽瘑鐮佽繘琛岀櫥褰曢獙璇併3銆佺櫥褰曟垚鍔燂紝杩涘叆涓荤晫闈紝鐐瑰嚮鑿滃崟鏍忓銆愭湇鍔″櫒銆戙4銆佹鏃跺湪瀛樺偍鏍忥紝鎵惧埌琛ㄧ┖闂撮夐」锛岀偣鍑绘墦寮銆5銆佸湪鎼滅储鏍忚緭鍏ヨ〃绌洪棿鍚嶇О锛岃繘琛屾悳绱6銆佹鏃跺湪涓嬫柟缁撴灉鏍忥紝鍙互鐪嬪埌琛ㄧ┖闂寸姸鎬併
