oracle如何查重復(fù)數(shù)據(jù)并顯示出來？

SELECT *

創(chuàng)新互聯(lián)是一家集網(wǎng)站建設(shè),桃城企業(yè)網(wǎng)站建設(shè),桃城品牌網(wǎng)站建設(shè),網(wǎng)站定制,桃城網(wǎng)站建設(shè)報價,網(wǎng)絡(luò)營銷,網(wǎng)絡(luò)優(yōu)化,桃城網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強企業(yè)競爭力?？沙浞譂M足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時我們時刻保持專業(yè)、時尚、前沿，時刻以成就客戶成長自我，堅持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實用型網(wǎng)站。

FROM t_info a

WHERE ((SELECT COUNT(*)

FROM t_info

WHERE Title = a.Title) 1)

ORDER BY Title DESC

一。查找重復(fù)記錄

1。查找全部重復(fù)記錄

Select * From 表 Where 重復(fù)字段 In (Select 重復(fù)字段 From 表 Group By 重復(fù)字段 Having Count(*)1)

2。過濾重復(fù)記錄(只顯示一條)

Select * From HZT Where ID In (Select Max(ID) From HZT Group By Title)

注：此處顯示ID最大一條記錄

二。刪除重復(fù)記錄

1。刪除全部重復(fù)記錄（慎用）

Delete 表 Where 重復(fù)字段 In (Select 重復(fù)字段 From 表 Group By 重復(fù)字段 Having Count(*)1)

2。保留一條（這個應(yīng)該是大多數(shù)人所需要的 ^_^）

Delete HZT Where ID Not In (Select Max(ID) From HZT Group By Title)

注：此處保留ID最大一條記錄

1、查找表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個字段（peopleId）來判斷

select * from people

where peopleId in (select peopleId from people group by peopleId having count(peopleId) 1)

2、刪除表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個字段（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、查找表中多余的重復(fù)記錄（多個字段）

select * from vitae a

where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) 1)

4、刪除表中多余的重復(fù)記錄（多個字段），只留有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、查找表中多余的重復(fù)記錄（多個字段），不包含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)

補充：

有兩個以上的重復(fù)記錄，一是完全重復(fù)的記錄，也即所有字段均重復(fù)的記錄，二是部分關(guān)鍵字段重復(fù)的記錄，比如Name字段重復(fù)，而其他字段不一定重復(fù)或都重復(fù)可以忽略。

1、對于第一種重復(fù)，比較容易解決，使用

select distinct * from tableName

就可以得到無重復(fù)記錄的結(jié)果集。

如果該表需要刪除重復(fù)的記錄（重復(fù)記錄保留1條），可以按以下方法刪除

select distinct * into #Tmp from tableName

drop table tableName

select * into tableName from #Tmp

drop table #Tmp

發(fā)生這種重復(fù)的原因是表設(shè)計不周產(chǎn)生的，增加唯一索引列即可解決。

2、這類重復(fù)問題通常要求保留重復(fù)記錄中的第一條記錄，操作方法如下

假設(shè)有重復(fù)的字段為Name,Address，要求得到這兩個字段唯一的結(jié)果集

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)

ORACLE有函數(shù)可以去掉字段里面的重復(fù)字符嗎

沒有,可以自己寫SQL,或者自定義函數(shù):

--自定義函數(shù):

SQL?create?or?replace?function?f(pstr?in?varchar2)?return?varchar2?is

2????v_newstr?varchar2(100)?:=?null;

3????i????????pls_integer?:=?1;

4??begin

5????for?i?in?1?..?length(pstr)?loop

6??????if?instr(v_newstr,?substr(pstr,?i,?1))?=?0?or?v_newstr?is?null?then

7????????v_newstr?:=?v_newstr?||?substr(pstr,?i,?1);

8??????end?if;

9????end?loop;

10????return?v_newstr;

11??end;

12??/

Function?created

SQL?with?tmp(col)?as

2???(select?'aaaabbbcccdefg'?from?dual?union?all?select?'adbdfre'?from?dual)

3??select?col,?f(col)?from?tmp

4??/

COL????????????F(COL)

--------------?--------------------------------------------------------------------------------

aaaabbbcccdefg?abcdefg

adbdfre????????adbfre

--SQL

SQL?

SQL?with?tmp(col)?as

2???(select?'aaaabbbcccdefg'?from?dual?union?all?select?'adbdfre'?from?dual)

3??select?col,?listagg(c)?within?group(order?by?sqrt_id)?as?col1

4????from?(select?col,?c,?max(sqrt_id)?as?sqrt_id

5????????????from?(select?t.col,

6?????????????????????????substr(t.col,?column_value,?1)?as?c,

7?????????????????????????column_value?as?sqrt_id

8????????????????????from?tmp?t,

9?????????????????????????table(cast(multiset

10????????????????????????????????????(select?level

11???????????????????????????????????????from?dual

12?????????????????????????????????????connect?by?level?=?length(t.col))?as

13????????????????????????????????????sys.odcinumberlist)))

14???????????group?by?col,?c)

15???group?by?col

16??/

COL????????????COL1

--------------?--------------------------------------------------------------------------------

aaaabbbcccdefg?abcdefg

adbdfre????????abdfre

oracle如何去除字符串中的重復(fù)字符

這個函數(shù)的功能主要是用于去除給定字符串中重復(fù)的字符串.在使用中需要指定字符串的分隔符.示例:

str := RemoveSameStr('zhang,Zhang,bao,Bao,bao,zhang', ',');

輸出: zhang,Zhang,bao,Bao

--SQL

str varchar2(1000);

currentIndex number;

startIndex number;

endIndex number;

type str_type is table of varchar2(30) index by binary_integer;

arr str_type;

Result varchar2(1000);

begin

-- 空字符串

if oldStr is null then

return('');

end if;

--字符串太長

if length(oldStr) 1000 then

return(oldStr);

end if;

str := oldStr;

currentIndex := 0;

startIndex := 0;

loop

currentIndex := currentIndex + 1;

endIndex := instr(str, sign, 1, currentIndex);

if (endIndex = 0) then

exit;

end if;

arr(currentIndex) := trim(substr(str,

startIndex + 1,

endIndex - startIndex - 1));

startIndex := endIndex;

end loop;

--取最后一個字符串:

arr(currentIndex) := substr(str, startIndex + 1, length(str));

--去掉重復(fù)出現(xiàn)的字符串:

for i in 1 .. currentIndex - 1 loop

for j in i + 1 .. currentIndex loop

if arr(i) = arr(j) then

arr(j) := '';

end if;

end loop;

end loop;

str := '';

for i in 1 .. currentIndex loop

if arr(i) is not null then

str := str || sign || arr(i);

--數(shù)組置空:

arr(i) := '';

end if;

end loop;

--去掉前面的標識符:

Result := substr(str, 2, length(str));

return(Result);

end RemoveSameStr;

轉(zhuǎn)載，僅供參考。

文章題目：oracle如何重復(fù)字符,oracle根據(jù)字段去重復(fù)
標題URL：http://sd-ha.com/article24/hooece.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供ChatGPT、網(wǎng)站維護、做網(wǎng)站、App設(shè)計、網(wǎng)站收錄、商城網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)