共計 2180 個字符，預計需要花費 6 分鐘才能閱讀完成。
今天就跟大家聊聊有關 oracle 中如何刪除重數據，可能很多人都不太了解，為了讓大家更加了解，丸趣 TV 小編給大家總結了以下內容，希望大家根據這篇文章可以有所收獲。
前期準備
創建測試表
create table salary(
staffid int,
staff varchar(15)
);
模擬重復數據
insert into salary values(1, a
insert into salary values(2, s
insert into salary values(3, ert
insert into salary values(4, d
insert into salary values(5, b
insert into salary values(1, a
insert into salary values(2, s
insert into salary values(3, ert
insert into salary values(4, d
insert into salary values(5, b
insert into salary values(1, a
insert into salary values(2, s
insert into salary values(3, ert
insert into salary values(4, d
insert into salary values(5, b
insert into salary values(10, aaaa
insert into salary values(20, sass
insert into salary values(30, erwt
insert into salary values(40, dsd
insert into salary values(50, bsdf
insert into salary values(1, oookkk
實驗一：模擬單個字段數據重復
select * from salary;
STAFFID STAFF
————— —————
1 oookkk
1 a
2 s
3 ert
4 d
5 b
1 a
2 s
3 ert
4 d
5 b
1 a
2 s
3 ert
4 d
5 b
10 aaaa
20 sass
30 erwt
40 dsd
50 bsdf
21 rows selected
1. 查出重復數據
方法一
SELECT *
FROM salary a
WHERE ((SELECT COUNT(*)
FROM salary
WHERE staffid = a.staffid) 1)
ORDER BY staffid
方法二
select *
from salary
where staffid in
(select staffid from salary group by staffid having count(staffid) 1)
刪除重復數據，只保留 1 條，其余全部刪除
方法一，通過 rowid 刪除
delete from salary
where staffid in (select staffid from salary group by staffid having count(staffid) 1)
and rowid not in (select min(rowid) from salary group by staffid having count(staffid) 1)
實驗二：模擬兩個個字段數據重復
1. 查詢重復記錄
方法一
select * from salary a
where (a.staffid,a.staff) in (select staffid,staff from salary group by staffid,staff having count(*) 1)
方法二
SELECT *
FROM salary a
WHERE ((SELECT COUNT(*)
FROM salary
WHERE staffid = a.staffid and staff=a.staff) 1)
ORDER BY staffid
結果，共 15 條
STAFFIDSTAFF
1
a
1
a
1
a
2
s
2
s
2
s
3
ert
3
ert
3
ert
4
d
4
d
4
d
5
b
5
b
5
b
2. 刪除重復數據，只保留 1 條，其余全部刪除
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)
3. 查看刪除后結果
select * from salary;
結果
STAFFIDSTAFF
1
oookkk
1
a
2
s
3
ert
4
d
5
b
10
aaaa
20
sass
30
erwt
40
dsd
50
bsdf
看完上述內容，你們對 oracle 中如何刪除重數據有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注丸趣 TV 行業資訊頻道，感謝大家的支持。
