oracle 分页删除,使用Oracle SQL 删除重复记录及分页 备案

bank_holidays 表结构

desc bank_holidays

Name                           Null     Type

------------------------------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

BANK_HOLIDAY_ID                NOT NULL NUMBER

BANK_HOLIDAY_DESC                       VARCHAR2(255)

BANK_HOLIDAY_DATE                       DATE

3 rows selected

分页：

select * from (

select * from bank_holidays where rownum<=5) where bank_holiday_id not in

(select bank_holiday_id from bank_holidays where rownum<=3)

删除重复数据：

delete bank_holidays where bank_holiday_id in(

select bank_holiday_id from bank_holidays where bank_holiday_date in

(

select  bank_holiday_date from bank_holidays  GROUP BY bank_holiday_desc, bank_holiday_date  having count (bank_holiday_desc) >= 2

)and

bank_holiday_id not in (

select min(bank_holiday_id) from bank_holidays  GROUP BY bank_holiday_desc, bank_holiday_date  having count (bank_holiday_desc) >= 2

)

)