问带有0记录的SQL显示用例
EN

Stack Overflow用户

提问于 2015-11-09 09:43:36

回答 2查看 45关注 0票数 0

好的，我有这样的声明：

select 
CASE    
    WHEN lm.location_name like 'HM%' THEN 'HMO'
    WHEN lm.location_name like 'LS%' or lm.location_name = 'R' THEN 'LSR'
    WHEN lm.location_name like 'SD%' or lm.location_name like 'St%' THEN 'SSO'
    WHEN lm.location_name like 'ME%' THEN 'MV'
END as Office, 
CASE County
    WHEN '1' THEN 'A'
    WHEN '2' THEN 'B'
    WHEN '3' THEN 'B'
    WHEN '4' THEN 'C'
ELSE p.COUNTY END as County,
COUNT(*) as [Count]
from CTE as a
join location_mstr as lm
    on lm.location_id = a.location_id
join person as p
    on p.person_id = a.person_id
where 
ROW = 1
and CONVERT(datetime,a.date) >= CONVERT(datetime,'2015-04-01')
and CONVERT(datetime,a.date) <DATEADD(day,1,CONVERT(datetime,'2015-04-30'))
group by 
CASE    
    WHEN lm.location_name like 'HM%' THEN 'HMO'
    WHEN lm.location_name like 'LS%' or lm.location_name = 'R' THEN 'LSR'
    WHEN lm.location_name like 'SD%' or lm.location_name like 'St%' THEN 'SSO'
    WHEN lm.location_name like 'ME%' THEN 'MV'
END,
CASE County
    WHEN '1' THEN 'A'
    WHEN '2' THEN 'B'
    WHEN '3' THEN 'B'
    WHEN '4' THEN 'C'
ELSE p.COUNTY END
order by Office

这给了我正确的结果。但是，如果结果为零，是否有可能出现位置、名称或县。

例如，如果HMO office和B县的组合没有结果，那么仍然显示为HMO _B_0，而不是一点也不显示？

tsql

sql-server-2008

有奖征集｜云上CPU玩转AIGC挑战赛

参加活动赢取洛斐键盘、小米电纸书、智能音响等鹅厂周边！

回答 2

Stack Overflow用户

回答已采纳

发布于 2015-11-09 09:57:09

这是在黑暗中拍摄的。如果CTE、location_mstr和person表中的结构和数据可用，我可能会提供更好的查询。

总的来说，使用all_office_county CTE，我们正在提取所有可能的office和county组合。然后，我们将该数据集作为主数据集，并与您在查询中成功获得的结果进行联接。在没有找到匹配的地方，我们使用0作为计数。

with all_office_county as 
(
select
    distinct 
    CASE    
        WHEN lm.location_name like 'HM%' THEN 'HMO'
        WHEN lm.location_name like 'LS%' or lm.location_name = 'R' THEN 'LSR'
        WHEN lm.location_name like 'SD%' or lm.location_name like 'St%' THEN 'SSO'
        WHEN lm.location_name like 'ME%' THEN 'MV'
    END as Office,
    CASE County
        WHEN '1' THEN 'A'
        WHEN '2' THEN 'B'
        WHEN '3' THEN 'B'
        WHEN '4' THEN 'C'
    ELSE p.COUNTY END as County
from CTE a
left join location_mstr as lm on lm.location_id = a.location_id
left join person as p p.person_id = a.person_id
),

main as (
select 
CASE    
    WHEN lm.location_name like 'HM%' THEN 'HMO'
    WHEN lm.location_name like 'LS%' or lm.location_name = 'R' THEN 'LSR'
    WHEN lm.location_name like 'SD%' or lm.location_name like 'St%' THEN 'SSO'
    WHEN lm.location_name like 'ME%' THEN 'MV'
END as Office, 
CASE County
    WHEN '1' THEN 'A'
    WHEN '2' THEN 'B'
    WHEN '3' THEN 'B'
    WHEN '4' THEN 'C'
ELSE p.COUNTY END as County,
COUNT(*) as [Count]
from CTE as a
join location_mstr as lm
    on lm.location_id = a.location_id
join person as p
    on p.person_id = a.person_id
where 
ROW = 1
and CONVERT(datetime,a.date) >= CONVERT(datetime,'2015-04-01')
and CONVERT(datetime,a.date) <DATEADD(day,1,CONVERT(datetime,'2015-04-30'))
group by 
CASE    
    WHEN lm.location_name like 'HM%' THEN 'HMO'
    WHEN lm.location_name like 'LS%' or lm.location_name = 'R' THEN 'LSR'
    WHEN lm.location_name like 'SD%' or lm.location_name like 'St%' THEN 'SSO'
    WHEN lm.location_name like 'ME%' THEN 'MV'
END,
CASE County
    WHEN '1' THEN 'A'
    WHEN '2' THEN 'B'
    WHEN '3' THEN 'B'
    WHEN '4' THEN 'C'
ELSE p.COUNTY END
order by Office
)

select aoc.office, aoc.county, coalesce(m.[Count], 0)
from all_office_county aoc
left join main m on aoc.office = m.office and aoc.county = m.county

票数 0

Stack Overflow用户

发布于 2015-11-09 09:56:47

创建一个包含所有可能结果的CTE。然后将查询LEFT JOIN到此CTE

;WITH CTE AS (
   SELECT x.o, y.c
   FROM (VALUES ('HMO'), ('LSR'), ('SSO')) AS x(o)
   CROSS JOIN (VALUES ('A'), ('B'), ('C')) AS y(c)
)
SELECT t1.o AS Office, t2.c AS County, COALESCE(t2.[Count], 0) AS [Count] 
FROM CTE AS t1
LEFT JOIN ( ... your query here ...) AS t2 
ON t1.o = t2.Office AND t1.c = t2.County