如果计数为 0 或未找到行，则返回每个"选择案例"选项的结果

Question

TSQL 2008r2. 我希望此查询始终返回三行。 一个用于选择案例中指定的每个项目：

SELECT CASE result 
WHEN 'A' then 'Group A' 
WHEN 'B' then 'Group B' 
WHEN 'C' then 'Group C' 
end as resultType, count(Result) as numberOfResults from masterGroups 
where theYear=2016 and postCode=3579 group by result;

ATM 它正确返回以下内容：

Group A    1
Group C    8

我希望它始终返回三行，无论它在主组中找到什么。 如果找不到三个项目之一，我希望它返回 0。所以在上面的情况下，它应该返回这个：

Group A    1
Group B    8
Group C    0

提前致谢

Answer 1

您真正想要的是一个包含所有结果类型的表。 如果没有此功能，我们可能会为此目的使用即时 CTE：

WITH results AS (
SELECT 'A' AS result UNION ALL
SELECT 'B' UNION ALL
SELECT 'C'
)
SELECT
CASE r.result 
WHEN 'A' THEN 'Group A' 
WHEN 'B' THEN 'Group B' 
WHEN 'C' THEN 'Group C' 
END AS resultType,
COUNT(m.result) AS numberOfResults
FROM results r
LEFT JOIN masterGroups m
ON r.result = m.result AND m.theYear = 2016 AND m.postCode = 3579
GROUP BY
r.result;

注： 将当前WHERE子句中的逻辑移动到左连接的ON条件。

Answer 2

您也可以通过以下代码实现相同的目的

SELECT CASE groups 
WHEN 'A' then 'Group A' 
WHEN 'B' then 'Group B' 
WHEN 'C' then 'Group C' 
END AS resultType, 
COUNT(Result) AS numberOfResults 
FROM (VALUES ('A'), ('B'), ('C')) r (groups)
LEFT JOIN masterGroups m ON m.Result = r.groups
AND theYear=2016 AND postCode=3579 
GROUP BY groups

或

FROM (SELECT 'A' UNION SELECT 'B' UNION SELECT 'C') r (groups)
LEFT JOIN masterGroups m ON m.Result = r.groups
AND theYear=2016 AND postCode=3579 
GROUP BY groups

Answer 3

您可以使用PIVOT和UNPIVOT：

select resultType,numberOfResults
from
(
select 'numresults' numresults,[A]'Group A',[B] 'Group B',[C] 'Group c'
from
( 
select result from masterGroups where theYear=2016 and postCode=3579
)as source
pivot
(
count(result) for result in ([A],[B],[C])
)as p
)ss
unpivot
(
numberOfResults
for resultType in ([Group A], [Group B], [Group c])
)up