SQL 如何返回 0 而不是 NULL 的计数（*）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8032775/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How return a count(*) of 0 instead of NULL
提问by alamodey
I have this bit of code:
我有这么一段代码：
SELECT Project, Financial_Year, COUNT(*) AS HighRiskCount
INTO #HighRisk
FROM #TempRisk1
WHERE Risk_1 = 3
GROUP BY Project, Financial_Year
where it's not returning any rows when the count is zero. How do I make these rows appear with the HighRiskCount set as 0?
当计数为零时，它不返回任何行。如何在 HighRiskCount 设置为 0 的情况下显示这些行？
回答by Adam Robinson
You can't select the values from the table when the row count is 0. Where would it get the values for the nonexistent rows?
当行数为 0 时，您无法从表中选择值。从哪里获取不存在的行的值？
To do this, you'll have to have another table that defines your list of valid Projectand Financial_Yearvalues. You'll then select from this table, perform a left joinon your existing table, then do the grouping.
为此，您必须有另一个表来定义有效值Project和Financial_Year值的列表。然后，您将从该表中进行选择，left join对现有表执行 a ，然后进行分组。
Something like this:
像这样的东西：
SELECT l.Project, l.Financial_Year, COUNT(t.Project) AS HighRiskCount
INTO #HighRisk
FROM MasterRiskList l
left join #TempRisk1 t on t.Project = l.Project and t.Financial_Year = l.Financial_Year
WHERE t.Risk_1 = 3
GROUP BY l.Project, l.Financial_Year
回答by Steve Mol
Wrap your SELECTQuery in an ISNULL:
将您的SELECT查询包装在一个ISNULL：
SELECT ISNULL((SELECT Project, Financial_Year, COUNT(*) AS hrc
INTO #HighRisk
FROM #TempRisk1
WHERE Risk_1 = 3
GROUP BY Project, Financial_Year),0) AS HighRiskCount
If your SELECTreturns a number, it will pass through. If it returns NULL, the 0will pass through.
如果您SELECT返回一个数字，它将通过。如果它返回NULL，0将通过。
回答by t-clausen.dk
Assuming you have your 'Project' and 'Financial_Year' where Risk_1 is different than 3, and those are the ones you intend to include.
假设您有您的“项目”和“财务年”，其中 Risk_1 与 3 不同，而这些是您打算包括的。
SELECT Project, Financial_Year, SUM(CASE WHEN RISK_1 = 3 THEN 1 ELSE 0 END) AS HighRiskCount
INTO #HighRisk
FROM #TempRisk1
GROUP BY Project, Financial_Year
Notice i removed the where part.
注意我删除了 where 部分。
By the way, your current query is not returning null, it is returning no rows.
顺便说一下，您当前的查询没有返回 null，它没有返回任何行。
回答by OMG Ponies
Use:
用：
SELECT x.Project, x.financial_Year,
COUNT(y.*) AS HighRiskCount
INTO #HighRisk
FROM (SELECT DISTINCT t.project, t.financial_year
FROM #TempRisk1
WHERE t.Risk_1 = 3) x
LEFT JOIN #TempRisk1 y ON y.project = x.project
AND y.financial_year = x.financial_year
GROUP BY x.Project, x.Financial_Year
The only way to get zero counts is to use an OUTERjoin against a list of the distinct values you want to see zero counts for.
获得零计数的唯一方法是对OUTER要查看零计数的不同值列表使用连接。
回答by Larry Lustig
SQL generally has a problem returning the values that aren'tin a table. To accomplish this (without a stored procedure, in any event), you'll need another table that contains the missing values.
SQL 通常在返回不在表中的值时会出现问题。为了实现这一点（无论如何，没有存储过程），您需要另一个包含缺失值的表。
Assuming you want one row per project / financial year combination, you'll need a table that contains each valid Project, Finanical_Year combination:
假设您希望每个项目/财政年度组合有一行，您将需要一个包含每个有效项目、Finanical_Year 组合的表：
SELECT HR.Project, HR.Financial_Year, COUNT(HR.Risk_1) AS HighRiskCount
INTO #HighRisk HR RIGHT OUTER JOIN ProjectYears PY
ON HR.Project = PY.Project AND HR.Financial_Year = PY.Financial_Year
FROM #TempRisk1
WHERE Risk_1 = 3
GROUP BY HR.Project, HR.Financial_Year
Note that we're taking advantage of the fact that COUNT() will only count non-NULL values to get a 0 COUNT result for those result set records that are made up only of data from the new ProjectYears table.
请注意，对于仅由新 ProjectYears 表中的数据组成的那些结果集记录，我们正在利用 COUNT() 仅对非 NULL 值进行计数的事实，以获得 0 COUNT 结果。
Alternatively, you might only one 0 count record to be returned per project(or maybe one per financial_year). You would modify the above solution so that the JOINed table has only that one column.
或者，您可能每个项目只返回一个 0 计数记录（或者每个 Financial_year 一个）。您将修改上述解决方案，以便 JOINed 表只有那一列。
回答by UnhandledExcepSean
Little longer, but what about this as a solution?
再长一点，但是作为解决方案呢？
IF EXISTS (
SELECT *
FROM #TempRisk1
WHERE Risk_1 = 3
)
BEGIN
SELECT Project, Financial_Year, COUNT(*) AS HighRiskCount
INTO #HighRisk
FROM #TempRisk1
WHERE Risk_1 = 3
GROUP BY Project, Financial_Year
END
ELSE
BEGIN
INSERT INTO #HighRisk
SELECT 'Project', 'Financial_Year', 0
END
回答by Jeremy Wiggins
SELECT Project, Financial_Year, ISNULL(COUNT(*), 0) AS HighRiskCount
INTO #HighRisk
FROM #TempRisk1
WHERE Risk_1 = 3
GROUP BY Project, Financial_Year
