熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

解析Oracle查詢和刪除JOB的SQL

2013-11-13 22:25:34  來源: Oracle 
本篇文章是對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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.