分类记录计数，包括零
分类记录计数，包括零
提问于 2020-07-21 11:58:00
我有一个表格，格式如下：
----------------------------------------------------
| Id | user_name | submitted | reviewed | returned |
---------------------------------------------------------
| 1 | tom | 01-01-2020 | 02-01-2020 | |
| 2 | mary | 01-15-2020 | | |
| 3 | joe | 01-25-2020 | 02-07-2020 | 03-04-2020 |
| 4 | tom | 01-07-2020 | | |
| 5 | tom | 01-04-2020 | | |
| 6 | mary | 01-16-2020 | | |
| 7 | joe | 02-08-2020 | 02-08-2020 | 03-07-2020 |
| 8 | mary | 01-05-2020 | 01-20-2020 | 03-19-2020 |
| 9 | joe | 01-21-2020 | 02-09-2020 | |
---------------------------------------------------------我想编写一个查询，统计每个用户的提交、审核和返回记录，其中" Submitted“是任何提交日期为not null的记录，而审核和返回的记录为空。“已审核”是指所有提交和审核日期不为空且返回日期为空的记录。"Returned是指提交、审核和返回日期不为空的任何记录。
期望的输出将如下所示：
-----------------------------------------------------
| user_name | # Submitted | # Reviewed | # Returned |
-----------------------------------------------------
| joe | 0 | 1 | 2 |
| mary | 2 | 0 | 1 |
| tom | 2 | 1 | 0 |
-----------------------------------------------------我尝试执行三个按user_name分组的独立计数查询，但这些查询没有找到零。我对sql非常陌生，因此任何帮助都将不胜感激。
回答 2
Stack Overflow用户
回答已采纳
发布于 2020-07-21 19:52:25
只需使用count()即可。根据您的示例数据，您可以单独查看每一列：
select user_name,
count(submitted) as num_submitted,
count(reviewed) as num_reviewed,
count(returned) as num_returned
from t
group by user_name;例如，这里没有示例，其中returned为非NULL，而其他两列中的任何一列为NULL。
如果这确实是可能的，您可以使用条件聚合：
select user_name,
count(submitted) as num_submitted,
sum(case when submitted is not null and reviewed is not null then 1 else 0 end) as num_reviewed,
sum(case when submitted is not null and reviewed is not null and returned is not null then 1 else 0 end) as num_returned
from t
group by user_name;你也可以使用count()，玩算术游戏：
select user_name,
count(submitted) as num_submitted,
count(day(submitted) + day(reviewed)) as num_reviewed,
count(day(submitted) + day(reviewed) + day(returned)) as num_returned
from t
group by user_name;这之所以有效，是因为如果值为NULL，则day()返回NULL。如果任何值为NULL，则+返回NULL。
Stack Overflow用户
发布于 2020-07-21 13:13:43
试试这个：
DECLARE @DataSource TABLE
(
[id] INT
,[user_name] NVARCHAR(128)
,[submitted] DATE
,[reviewed] DATE
,[returned] DATE
);
INSERT INTO @DataSource ([id], [user_name], [submitted], [reviewed], [returned])
VALUES (1, 'tom', '01-01-2020', '02-01-2020', NULL)
,(2, 'mary', '01-15-2020', NULL, NULL)
,(3, 'joe', '01-25-2020', '02-07-2020', '03-04-2020')
,(4, 'tom', '01-07-2020', NULL, NULL)
,(5, 'tom', '01-04-2020', NULL, NULL)
,(6, 'mary', '01-16-2020', NULL, NULL)
,(7, 'joe', '02-08-2020', '02-08-2020', '03-07-2020')
,(8, 'mary', '01-05-2020', '01-20-2020', '03-19-2020')
,(9, 'joe', '01-21-2020', '02-09-2020', NULL);
SELECT [user_name]
,SUM(IIF([returned] IS NULL AND [reviewed] IS NULL AND [submitted] IS NOT NULL, 1, 0)) AS [ # Submitted]
,SUM(IIF([returned] IS NULL AND [reviewed] IS NOT NULL AND [submitted] IS NOT NULL, 1, 0)) AS [# Reviewed]
,SUM(IIF([returned] IS NOT NULL AND [reviewed] IS NOT NULL AND [submitted] IS NOT NULL, 1, 0)) AS [# Returned]
FROM @DataSource
GROUP BY [user_name];页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/63007133
复制相关文章
相似问题
腾讯云开发者
