返回给定员工的行，即使sql developer中不存在数据

Question

我的 sql 查询为给定员工返回特定周的正确结果

例如，我的员工列表是(员工1，员工2，员工3，员工4)

如果emp4在该周内未完成任何工作，则该员工的行根本不会返回。

我的疑问如下:

select a.creator_login ,
sum (case when a.activitytype = 'NCE- Installation' then a.duration else 0 end) as NCE_Installation ,
sum (case when a.activitytype = 'NCE- Migration' then a.duration else 0 end) as NCE_Migration ,
sum (case when a.activitytype = 'NCE-Circuit Testing' then a.duration else 0 end) as NCE_Circuit_Testing ,
sum (case when a.activitytype = 'NCE-Communication - External' then a.duration else 0 end) as NCE_Communication_External,
sum (case when a.activitytype = 'NCE-Communication - Internal' then a.duration else 0 end) as NCE_Communication_Internal,
sum (case when a.activitytype = 'Exception' then a.duration else 0 end) as Exception,
sum (case when a.activitytype = 'NCE-Configuration' then a.duration else 0 end) as NCE_Configuration,
sum (case when a.activitytype = 'NCE-Design Reqt Gathering' then a.duration else 0 end) as NCE_Design_Reqt_Gathering,
sum (case when a.activitytype = 'NCE-Documentation' then a.duration else 0 end) as NCE_Documentation,
sum (case when a.activitytype = 'Notes' then a.duration else 0 end) as Notes,
sum (case when b.openingcode = 'GOC Acceptance' then a.duration else 0 end) as  GOC_Acceptance,
sum (case when a.activitytype = 'To Do' then a.duration else 0 end) as To_Do 
from vware.snap_ticketactivities a , vware.snap_troubletickets b 
where a.ticketrowid  = b.ticketrowid  
and a.creator_login in ('AMITTAL','HSHARMA','NKHAN','PKSINGH','PPATNANA','PTHAKUR','SDAS','SPATEL','VDASS','VVIGNESHWARAN','AOAK') and a.created between  trunc(sysdate-12)  and trunc(sysdate-6 )
group by a.creator_login

如果该周的员工记录仍然不存在，则员工姓名应显示为0

Answer 1

试试这个：

select emp_list.creator_login,
       sum(case
             when a.activitytype = 'NCE- Installation' then
              a.duration
             else
              0
           end) as NCE_Installation,
       sum(case
             when a.activitytype = 'NCE- Migration' then
              a.duration
             else
              0
           end) as NCE_Migration,
       sum(case
             when a.activitytype = 'NCE-Circuit Testing' then
              a.duration
             else
              0
           end) as NCE_Circuit_Testing,
       sum(case
             when a.activitytype = 'NCE-Communication - External' then
              a.duration
             else
              0
           end) as NCE_Communication_External,
       sum(case
             when a.activitytype = 'NCE-Communication - Internal' then
              a.duration
             else
              0
           end) as NCE_Communication_Internal,
       sum(case
             when a.activitytype = 'Exception' then
              a.duration
             else
              0
           end) as Exception,
       sum(case
             when a.activitytype = 'NCE-Configuration' then
              a.duration
             else
              0
           end) as NCE_Configuration,
       sum(case
             when a.activitytype = 'NCE-Design Reqt Gathering' then
              a.duration
             else
              0
           end) as NCE_Design_Reqt_Gathering,
       sum(case
             when a.activitytype = 'NCE-Documentation' then
              a.duration
             else
              0
           end) as NCE_Documentation,
       sum(case
             when a.activitytype = 'Notes' then
              a.duration
             else
              0
           end) as Notes,
       sum(case
             when b.openingcode = 'GOC Acceptance' then
              a.duration
             else
              0
           end) as GOC_Acceptance,
       sum(case
             when a.activitytype = 'To Do' then
              a.duration
             else
              0
           end) as To_Do
  from (select 'AMITTAL' creator_login
          from dual
        union all
        select 'HSHARMA'
          from dual
        union all
        select 'NKHAN'
          from dual
        union all
        select 'PKSINGH'
          from dual
        union all
        select 'PPATNANA'
          from dual
        union all
        select 'PTHAKUR'
          from dual
        union all
        select 'SDAS'
          from dual
        union all
        select 'SPATEL'
          from dual
        union all
        select 'VDASS'
          from dual
        union all
        select 'VVIGNESHWARAN'
          from dual
        union all
        select 'AOAK' from dual) emp_list
  left outer join vware.snap_ticketactivities a on emp_list.creator_login =
                                                   a.creator_login
 inner join vware.snap_troubletickets b on a.ticketrowid = b.ticketrowid
 where nvl(a.created, trunc(sysdate - 12)) between trunc(sysdate - 12) and
       trunc(sysdate - 6)
 group by emp_list.creator_login;