oracle常用sql

--备份tbluser的数据到tbluser_bak中
create table tbluser_bak as select * from tbluser; --此处不加as也行

--删除备份表的数据
truncate table tbluser_bak;

--还原表数据
insert into tbluser_bak select * from tbluser ;

--查看数据是否恢复
select * from tbluser_bak;
--查询重复数据不用having

select * from SCH_SC_OTHERTIME where PERSON_ID in(select PERSON_ID from (select PERSON_ID, DEPT_ID, NATURE , count(1)as num
from SCH_SC_OTHERTIME
group by PERSON_ID, DEPT_ID, NATURE ) where num>1)

--查询重复数据用having

select e.person_id, e.dept_id, e.nature
from sch_sc_othertime e
group by e.person_id, e.dept_id, e.nature
having count(*) > 1

--查看根据指定字段查询重复数据保留最大值

delete from sch_sc_weekresiduetime e
 where (e.person_id, e.dept_id, e.nature, e.weekdate, e.residuenumber) not in
       (select e.person_id,
               e.dept_id,
               e.nature,
               e.weekdate,
               max(e.residuenumber)
          from sch_sc_weekresiduetime e
         group by e.person_id, e.dept_id, e.nature, e.weekdate)
   and (e.person_id, e.dept_id, e.nature, e.weekdate) in
       (select e.person_id, e.dept_id, e.nature, e.weekdate
          from (select e.person_id,
                       e.dept_id,
                       e.nature,
                       e.weekdate,
                       count(1) as num
                  from sch_sc_weekresiduetime e
                 group by e.person_id, e.dept_id, e.nature, e.weekdate) e
         where e.num > 1);

--删除重复数据

delete from sch_sc_weekresiduetime e
 where (e.person_id, e.dept_id, e.nature, e.weekdate) in
       (select e.person_id, e.dept_id, e.nature, e.weekdate
          from (select e.person_id,
                       e.dept_id,
                       e.nature,
                       e.weekdate,
                       count(1) as num
                  from sch_sc_weekresiduetime e
                 group by e.person_id, e.dept_id, e.nature, e.weekdate) e
         where e.num > 1)
   and rowid not in (select max(rowid) from sch_sc_weekresiduetime);

--查看根据指定字段查询重复数据保留最大值

delete from sch_sc_weekresiduetime e
where (e.person_id, e.dept_id, e.nature,e.weekdate, e.residuenumber) not in
(select e.person_id, e.dept_id, e.nature,e.weekdate, max(e.residuenumber)
from sch_sc_weekresiduetime e
group by e.person_id, e.dept_id, e.nature,e.weekdate
having count(*) > 1)
and (e.person_id, e.dept_id, e.nature,e.weekdate) in
(select e.person_id, e.dept_id, e.nature,e.weekdate
from sch_sc_weekresiduetime e
group by e.person_id, e.dept_id, e.nature,e.weekdate
having count(*) > 1);

--删除重复数据

delete from sch_sc_weekresiduetime e
where (e.person_id, e.dept_id, e.nature,e.weekdate) in
(select e.person_id, e.dept_id, e.nature,e.weekdate
from sch_sc_weekresiduetime e
group by e.person_id, e.dept_id, e.nature,e.weekdate
having count(*) > 1)
and rowid not in (select max(rowid)
from sch_sc_weekresiduetime e
group by e.person_id, e.dept_id, e.nature,e.weekdate
having count(*) > 1);