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

EN

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

;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