Oracle:岗位复杂查询一例(原创)

业务描述


查询以下两类人员的人员信息(如有重复，也只显示一次)：
1.在“工人岗”中：井下作业>30个月，高空作业>20个月的员工(根据start_d，end_d计算月数)
2.都在“工人岗”和“管理岗”工作过的女性员工。
业务数据一：人员基本信息(性别：1-男 0-女)
select employee_id, name , sex_c from p_employee

EMPLOYEE_ID        NAME        SEX_C
0001        李四        1
0002        张三        1
0003        亿力        1
0004        流星        1
0005        王老六        1
0006        KU        1
0007        MG        0
0008        test1        0



业务数据二：岗位历史信息
select h.employee_id,h.start_d,h.end_d,h.worktype_c,h.especial_worktype,h.station_type_c
from  p_employ_history h

EMPLOYEE_ID        START_D        END_D        WORKTYPE_C        ESPECIAL_WORKTYPE        STATION_TYPE_C
0015        2000-1-1        2001-1-1        井下        1        工人岗
0015        2000-3-1                        高空        0        工人岗
0006        2002-1-2        2004-1-2        井下        1        工人岗
0005        2003-7-1        2005-7-1        井下        1        工人岗
0001        2005-1-1        2005-3-1        高空        1        工人岗
0002        2005-1-1                         井下        1        工人岗
0003        2005-1-1        2005-1-1                0        管理岗
0004        2005-1-1        2005-1-1                0        管理岗
0005        2005-1-1                        0        管理岗
0006        2003-1-1        2005-1-1        高空        1        工人岗
0006        2006-1-1                        井下        1        工人岗
0007        2005-1-1                        0        管理岗
0007        2003-1-1        2005-1-1        高空        1        工人岗

业务数据查询：人员岗位信息查询(便于设计参考用)

select t.employee_id,t.sex_c,h.start_d,h.end_d,h.worktype_c,h.especial_worktype,h.station_type_c
from p_employee t, p_employ_history h
where t.employee_id=h.employee_id

EMPLOYEE_ID        SEX_C        START_D        END_D        WORKTYPE_C        ESPECIAL_WORKTYPE        STATION_TYPE_C
0001        1        2005-1-1        2005-3-1        高空        1        工人岗
0002        1        2005-1-1                         井下        1        工人岗
0003        1        2005-1-1        2005-1-1                0        管理岗
0004        1        2005-1-1        2005-1-1                0        管理岗
0005        1        2003-7-1        2005-7-1        井下        1        工人岗
0005        1        2005-1-1                                 0        管理岗
0006        1        2003-1-1        2005-1-1        高空        1        工人岗
0006        1        2006-1-1                         井下        1        工人岗
0006        1        2002-1-2        2004-1-2        井下        1        工人岗
0007        0        2005-1-1                                 0        管理岗
0007        0        2003-1-1        2005-1-1        高空        1        工人岗
0015        0        2000-3-1                        高空        0        工人岗
0015        0        2000-1-1        2001-1-1        井下        1        工人岗

分析与解决

本着各个视图功能分开的原则(这样便于用union来合并不同数据集)：

视图1：查询工人岗中满足条件的人员
create or replace view v_employ_01 as
select "EMPLOYEE_ID","WORKTYPE_C","MONTHCOUNT" from (
  select t.employee_id,worktype_c,round(sum(months_between(nvl(h.end_d,sysdate),h.start_d)),0) monthCount
  from p_employee t, p_employ_history h
  where t.employee_id=h.employee_id
  and h.worktype_c is not null
  group by t.employee_id,h.worktype_c
) a
where (worktype_c = '井下' and monthCount>30)
 or   (worktype_c = '高空' and monthCount>20 )



视图2(先没有限制性别)：查询在管理岗和工人岗都工作过的员工
create or replace view v_employ_02 as
select a.employee_id from (
select h.employee_id
  from p_employ_history h
  where h.station_type_c in ('管理岗','工人岗')
  group by h.employee_id,h.station_type_c
  ) a
  group by a.employee_id
  having count(*)>1
  
视图3：查询满足条件的人员ID
 create or replace view query_gw as
select t.EMPLOYEE_ID
  from v_employ_01 t
union
select e.employee_id
  from v_employ_02 v, p_employee e
 where v.employee_id = e.employee_id
   and e.sex_c = 0
   
1.用union合并，并去除相同人员ID
1.在union后面的数据集中再限制性别为女(e.sex_c=0)
  
结果查询：根据id查出人员信息


select e.employee_id,e.name
from query_gw t,p_employee e
where t.EMPLOYEE_ID=e.employee_id

EMPLOYEE_ID NAME
0006                          KU
0007                        MG
0015                        test18