如何在GROUP BY语句中返回零计数

Question

我希望COUNT和GROUP BY语句返回零值

下面是我尝试做的事情的简化版本：

SELECT DISTINCT 
,r.Team
,r.Peron_ID
,'Person Status' AS 'VariableType'
, CASE
WHEN p.STATUS= 'Y' THEN 'Good'
WHEN p.STATUS = 'N' THEN 'Bad'
WHEN p.STATUS IS NULL OR p.STATUS NOT IN ('Y','N') THEN 'Invalid'
END AS VariableA
,NULL AS VariableB
into #TC
from    Test r
INNER JOIN Person p ON r.RECORD_NUMBER = p.RECORD_NUMBER
Select 
,t.Team
,t.Person_ID
,VariableType
,variableA
,ISNULL(count(t.Person_ID),0) as Count
from #TC t
Group by t.team, t.Person_ID, variabletype, VariableA

举个简单的例子，在下表中，Person1为每个VariableA都有一个数字，但Person2没有为它们中的任何一个输入：

+--------+-------------+---------------+-----------+-------+
| t.Team | t.Person_ID | VariableType  | VariableA | Count |
|--------+-------------+---------------+-----------+-------+
| Team1  | Person1     | Person Status | Good      |     2 |
| Team1  | Person1     | Person Status | Bad       |     3 |
| Team1  | Person1     | Person Status | Invalid   |     2 |
| Team1  | Person2     | Person Status | NULL      | NULL  |
+--------+-------------+---------------+-----------+-------+

我应该如何让Person2的好、坏和无效计数显示为0？

请将上面的代码视为一种伪代码，我已经改写并简化了我的实际代码，这是特定于公司的，依赖于大量的类型转换和大小写，所以语法可能不准确。

谢谢你的回复，罗斯。Person2根本没有记录他的P.STATUS。这就是为什么他只是显示为空，我想无论如何。

我预期(或只是希望(的结果是这样的：

+--------+-------------+---------------+-----------+-------+
| t.Team | t.Person_ID | VariableType  | VariableA | Count |
+--------+-------------+---------------+-----------+-------+
| Team1  | Person1     | Person Status | Good      |     2 |
| Team1  | Person1     | Person Status | Bad       |     3 |
| Team1  | Person1     | Person Status | Invalid   |     2 |
| Team1  | Person2     | Person Status | Good      |     0 |
| Team1  | Person2     | Person Status | Bad       |     0 |
| Team1  | Person2     | Person Status | Invalid   |     0 |
+--------+-------------+---------------+-----------+-------+

Answer 1

https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms175997(v=sql.90(

COUNT总是返回一个int数据类型值。COUNT_BIG总是返回bigint数据类型值。

Count始终不是空值

多重结果您的状态

DECLARE @a TABLE (a int, status varchar(255))
INSERT INTO @a VALUES (1, 'Good')
SELECT 
a.a,
x.Status, 
SUM((CASE WHEN a.Status =x.Status THEN 1 ELSE 0 END)) Cnt
FROM @a a 
CROSS JOIN
(
SELECT 'Good' UNION ALL
SELECT 'Bad' UNION ALL
SELECT 'Invalid'
) as x(Status)
GROUP  BY 
a.a,
x.Status

Answer 2

如果你想让variableA的3个唯一值显示在每个人/团队/变量类型的组合中，你需要用它们创建一个内联视图，然后在CROSS JOIN:中使用它

CREATE TABLE #Test(
[RECORD_NUMBER] [int] NOT NULL,
[Team] [nvarchar](50) NULL,
[Person_ID] [int] NULL
);
CREATE TABLE #Person(
[RECORD_NUMBER] [int] NOT NULL,
[STATUS] [nvarchar](1) NULL
);
SELECT r.Team
,r.Person_ID
,'Person Status' AS 'VariableType'
, CASE
WHEN p.STATUS= 'Y' THEN 'Good'
WHEN p.STATUS = 'N' THEN 'Bad'
WHEN p.STATUS IS NULL and p.RECORD_NUMBER IS NULL THEN NULL
WHEN p.STATUS IS NULL OR p.STATUS NOT IN ('Y','N') THEN 'Invalid'
END AS VariableA
,NULL AS VariableB
into #TC
from Test r 
LEFT JOIN Person p ON r.RECORD_NUMBER = p.RECORD_NUMBER;
SELECT 
t.Team,
t.Person_ID,
t.VariableType,
VarValues.VariableA, 
SUM((CASE WHEN t.VariableA = VarValues.VariableA THEN 1 ELSE 0 END)) Cnt
FROM #TC t
CROSS JOIN
(
SELECT 'Good' UNION ALL
SELECT 'Bad' UNION ALL
SELECT 'Invalid'
) as VarValues(VariableA)
GROUP  BY 
t.Team,
t.Person_ID,
t.VariableType,
VarValues.VariableA
ORDER BY t.Person_ID, VarValues.VariableA;
DROP TABLE #TC;
DROP TABLE #Test;
DROP TABLE #Person;