oracle sql获取漏号集、获取部门下所有子部门等汇总

oralce日期格式化

select to_char（sysdate,'yyyy-MM-DD'） from dual;

增加字段内容

--增加
update sys_user set role_no=role_no||',998' where 1=1
--减少
update sys_user set role_no=replace(role_no,',998','') where dept_no 1=1

获取部门下所有子部门

样例1：select * from sys_dept Start with  dept_no='626' Connect by  prior  dept_no=parent_no

样例2：select * from sys_dept where dept_type=1 Start with  dept_no='600' Connect by  prior  dept_no=parent_no

获取对应年度、类型的漏号

select t1.seq from (select rownum seq from dual where 1=1 
     connect by rownum <= (select max(seq) from OA_QS_FORM where year='"+year+"' and zy_num_class='"+zyNumClass+"')) t1 
     left join (select 0 min_seq, max(seq) max_seq from OA_QS_FORM where year='"+year+"' and zy_num_class='"+zyNumClass+"') t2 on 1=1 
     where t1.seq between t2.min_seq + 1 and t2.max_seq - 1 and not exists (select null from OA_QS_FORM t3 where t3.year='"+year+"' and t3.zy_num_class='"+zyNumClass+"' and t3.seq = t1.seq) 
      order by t1.seq

备注：表 OA_QS_FORM 、字段year年度、zy_num_class类型