数据值为0时缺少行

由 bjstry发布于 2020-05-18 08:26:06

我试图在查询中进行分组，但是每次数据为0时，分组都不显示整个行。我该如何解决？

这是常规查询

select 
    COUNT(DISTINCT sr.sr_number) AS NEW_CASES
FROM table sr
where 
    sr.business_unit in('BU1')
    and OPENED_DATE < trunc(current_Date)
    and OPENED_DATE > trunc(current_date -2)
    and sr.status = 'Open'

输出是

NEW_CASES
0

但是当我做一个分组时。整行不见了。

select 
    COUNT(DISTINCT sr.sr_number) AS NEW_CASES,
    sr.business_unit
FROM table sr
where 
    sr.business_unit in('BU1','BU2','BU3' )
    and OPENED_DATE < trunc(current_Date)
    and OPENED_DATE > trunc(current_date -2)
    and sr.status = 'Open'
group by sr.business_unit

按输出分组是

New_CASES    BUSINESS_UNIT
      200    BU2
      300    BU3

所需的输出：

New_CASES    BUSINESS_UNIT
        0    BU1
      200    BU2
      300    BU3

类敏思

2020-05-18 08:26:06

Use a left join. In Oracle, this would look like

select b.business_unit, COUNT(DISTINCT sr.sr_number) AS NEW_CASES
from (select 'BU1' as business_unit from dual union all
      select 'BU2' as business_unit from dual union all
      select 'BU3' as business_unit from dual 
     ) b left join
     sr
     on sr.business_unit = b.business_unit and
        sr.OPENED_DATE < trunc(current_Date) and
        sr.OPENED_DATE > trunc(current_date -2) and
        sr.status = 'Open'
group by b.business_unit

女人!依旧

2020-05-18 08:26:06

One option is to start from a fixed list of values, and then bring the table with a left join, like so:

select b.business_unit, count(distinct t.sr_number) as new_cases
from (
    select 'bu1' business_unit from dual
    union all select 'bu2' from dual
    union all select 'bu3' from dual
) b
left join mytable t
    on  t.business_unit = b.business_unit
    and t.opened_date > trunc(current_date -2)
    and t.opened_date < trunc(current_date)
    and t.status = 'open'
group by b.business_unit

bjstry

这家伙很懒，什么都没留下

积分
0
话题
0
评论
3230
注册排名
1334