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

Question

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

例如。我的员工列表是(emp1，emp2，emp3，emp4)

如果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;