使用 where 和 group by 子句计算 sql 中的空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9871368/
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
counting null values in sql with where and group by clause
提问by dragan.stepanovic
Here is the sample data for test table I have
这是我拥有的测试表的示例数据
[childId] [parentId] [present]
1 10 0
2 11 1
3 NULL 0
4 NULL 0
NULL 10 0
Now, for all the parentIds (including those with NULL) I want to count all the present childIds
现在，对于所有的 parentIds（包括那些为 NULL 的），我想计算所有当前的 childIds
select parentId, count(childId) as nbr
from TestTable
where present=1 or parentId is NULL
group by parentId
The result I get is
我得到的结果是
parentId nbr
NULL 2
11 1
Same count number (nbr) I get for both present=1 and present=0. It seems that I cannot impose a condition for the NULLgrouped value.
What's the mistake I'm making and what's the solution for the query I want to make?
nbr我为 present=1 和 present=0 得到相同的计数数 ( )。似乎我不能对NULL分组值强加条件。
我犯了什么错误，我想提出的查询的解决方案是什么？
UPDATE:
更新：
Since the test case I gave is not representative, until I recreate the real situation in better way, I'll try to explain the problem I'm facing.
由于我给出的测试用例不具有代表性，在我以更好的方式重现真实情况之前，我将尝试解释我所面临的问题。
I created a view with full outer join, so I have some records having NULLfor childIdand some records having NULLfor parentId.
Now, I want to list all of the parentIdvalues (both NULLand non NULL) and for each of them, count all the records that, for example, have present=1.
If I use where present=1condition, it eliminates NULL values from result set, i.e. I won't have result record NULL|xfor parentId|nbr.
I solved this with union all. In first select I have where present=1and in second select I have where present=1 and parentId is NULL.
我创建了一个带有完全外连接的视图，所以我有一些记录有NULLforchildId和一些记录有NULLfor parentId。
现在，我想列出所有parentId值（包括NULL和非NULL），并为每个值计算所有记录，例如，具有present=1. 如果我使用where present=1的条件，它消除了从结果集中NULL值，即我不会有结果记录NULL|x了parentId|nbr。
我用union all. 在第一个选择中我有where present=1，在第二个选择中我有where present=1 and parentId is NULL。
select parentId, count(childId) as nbr
from TestTable
where present=0
group by parentId
union all
select parentId, count(childId) as nbr
from TestTable
where present=0 and parentId is NULL
group by parentId
The result I get is
我得到的结果是
[parentId] [nbr]
NULL 2
10 1
NULL 2
The only problem with this (besides duplicated record for parentId=NULL), is that if there are no records with parentId=NULLand present=1, in result I won't have record with NULL | 0for parentId|nbrand I want to list ALL parentIds, both NULLand not NULL.
So to sum it up I need to have this format of output for present=1
唯一的问题（除了 for 的重复记录parentId=NULL）是，如果没有带parentId=NULLand 的记录present=1，结果我不会有NULL | 0for 的记录，parentId|nbr我想列出所有 parentIds， bothNULL和 not NULL。
所以总而言之，我需要有这种格式的输出present=1
[parentId] [nbr]
NULL 0
10 0
11 1
and this one for present=0
而这个是为了 present=0
[parentId] [nbr]
NULL 2
10 2
11 0
Any help?
有什么帮助吗？
采纳答案by cairnz
where present=1 or parentId is NULL
That would be it. Either you have Present = 1 or parentID is null. There's no clause saying that parentID should be null andpresent should be 1.
就是这样。您有 Present = 1 或 parentID 为空。没有任何条款说 parentID 应该为 null ，present 应该为 1。
What exactly are you trying to accomplish?
你到底想完成什么？
To list all non-null parentID that has present = 1 and all null parentID regardless of present, you can use your where clause, but edit your group by to GROUP BY parentid, present. This way you will show all non-null with present 1, and all null, both present 0 and 1.
要列出所有具有 present = 1 的非空 parentID 和所有空 parentID（无论是否存在），您可以使用 where 子句，但通过 to 编辑您的组GROUP BY parentid, present。通过这种方式，您将显示所有非空值和当前 1，以及所有空值，同时存在 0 和 1。
UPDATE: Based on your new requirements, you need to also group for non-existing combinations. Nowhere in your dataset is there a NULL, 1 value - so your ordinary way of looking at it won't make much sense. You need to separate the parentid and present from eachother.
更新：根据您的新要求，您还需要对不存在的组合进行分组。您的数据集中没有任何地方有 NULL, 1 值 - 所以您查看它的普通方式没有多大意义。您需要将 parentid 和 present 彼此分开。
One way to do this is to simply do the following:
一种方法是简单地执行以下操作：
SELECT parentID, Pres1.nbr_pres_1, Pres0.nbr_pres_0
FROM t
OUTER APPLY (SELECT COUNT(1) as nbr_pres_1 FROM t t1 WHERE
coalesce( t.parentid , -999) = coalesce(t1.parentid ,-999) and present=1 ) Pres1
OUTER APPLY (SELECT COUNT(1) as nbr_pres_0 FROM t t0 WHERE
coalesce( t.parentid , -999) = coalesce(t0.parentid ,-999) and present=0 ) Pres0
GROUP BY t.ParentID, Pres1.nbr_pres_1, Pres0.nbr_pres_0
This is based on testing using sqlfiddle and your sample data set. http://sqlfiddle.com/#!3/8d7f6/29
这是基于使用 sqlfiddle 和您的示例数据集进行的测试。http://sqlfiddle.com/#!3/8d7f6/29
回答by Peter
Use a SUM aggregation of the present field, casting the bit field to integer, as follows:
使用当前字段的 SUM 聚合，将位字段转换为整数，如下所示：
SELECT parentId, SUM(CAST(present AS INTEGER)) as nbr
FROM TestTable
GROUP BY parentId
回答by jb10210
If this is the result you are looking for:
如果这是您要查找的结果：
pId nbr
NULL 1
10 0
11 1
Then I think this is the sql you are looking for(not tested yet, no server available):
然后我认为这是您正在寻找的 sql（尚未测试，没有可用的服务器）：
select distinct
parentId as pId,
(select count(*) from TestTable where parentId=pId and present=1) as nbr
from TestTable
group by parentId
回答by Shailesh
Try below mentioned code snippet.
尝试下面提到的代码片段。
SELECT ParentId, COUNT(ChildId) nbr FROM SO
Where (Present = 1) OR (ParentId IS NULL AND Present = 1) Group by ParentId
其中 (Present = 1) OR (ParentId IS NULL AND Present = 1) 按 ParentId 分组
--OR You can try below mentioned code as well.
- 或者您也可以尝试下面提到的代码。
SELECT ParentId, COUNT(ChildId) nbr FROM SO
Where Present = 1 Group by ParentId
Where Present = 1 Group by ParentId
