解析Oracle查詢和刪除JOB的SQL

本篇文章是對Oracle查詢和刪除JOB的SQL的實現方法進行了詳細的分析介紹需要的朋友參考下  

　　查詢及刪除重復記錄的SQL語句
查找表中多余的重復記錄重復記錄是根據單個字段（peopleId）來判斷
select * from people
where peopleId in (select   peopleId from   people group by   peopleId having count(peopleId) > )

刪除表中多余的重復記錄重復記錄是根據單個字段（peopleId）來判斷只留有rowid最小的記錄
delete from people
where peopleId in (select   peopleId from people group by   peopleId   having count(peopleId) > )
and rowid not in (select min(rowid) from   people group by peopleId having count(peopleId )>)
注:rowid為oracle自帶不用該
查找表中多余的重復記錄（多個字段）
select * from vitae a
where (apeopleIdaseq) in   (select peopleIdseq from vitae group by peopleIdseq having count(*) > )
 
刪除表中多余的重復記錄（多個字段）只留有rowid最小的記錄
delete from vitae a
where (apeopleIdaseq) in   (select peopleIdseq from vitae group by peopleIdseq having count(*) > )
and rowid not in (select min(rowid) from vitae group by peopleIdseq having count(*)>)
查找表中多余的重復記錄（多個字段）不包含rowid最小的記錄
select * from vitae a
where (apeopleIdaseq) in   (select peopleIdseq from vitae group by peopleIdseq having count(*) > )
and rowid not in (select min(rowid) from vitae group by peopleIdseq having count(*)>)
(二)
比方說
在A表中存在一個字段“name”
而且不同記錄之間的“name”值有可能會相同
現在就是需要查詢出在該表中的各記錄之間“name”值存在重復的項
Select NameCount(*) from A Group By Name Having Count(*) >
如果還查性別也相同大則如下:
Select NamesexCount(*) from A Group By Namesex Having Count(*) >
(三)
方法一
declare @max integer@id integer
declare cur_rows cursor local for select 主字段count(*) from 表名 group by 主字段 having count(*) >
open cur_rows
fetch cur_rows into @id@max
while @@fetch

From:http://tw.wingwit.com/Article/program/Oracle/201311/19083.html