Oracle：如何计算空行和非空行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5512691/
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
Oracle: How to count null and non-null rows
提问by Svish
I have a table with two columns that might be null(as well as some other columns). I would like to count how many rows that have column a, b, both and neither columns set to null.
我有一个包含两列的表格null（以及其他一些列）。我想计算有多少行将 a 列、b 列、两列都设置为空。
Is this possible with Oracle in one query? Or would I have to create one query for each? Can't use group byor some other stuff I might not know about for example?
Oracle 在一个查询中可以做到这一点吗？或者我是否必须为每个创建一个查询？例如，不能使用group by或其他一些我可能不知道的东西？
回答by Vincent Malgrat
COUNT(expr)will count the number of rows where expris not null, thus you can count the number of nulls with expressions like these:
COUNT(expr)将计算expr不为空的行数，因此您可以使用以下表达式计算空值的数量：
SELECT count(a) nb_a_not_null,
count(b) nb_b_not_null,
count(*) - count(a) nb_a_null,
count(*) - count(b) nb_b_null,
count(case when a is not null and b is not null then 1 end)nb_a_b_not_null
count(case when a is null and b is null then 1 end) nb_a_and_b_null
FROM my_table
回答by a_horse_with_no_name
Something like this:
像这样的东西：
SELECT sum(case
when a is null and b is null then 1
else 0
end) as both_null_count,
sum(case
when a is null and b is not null then 1
else 0
end) as only_a_is_null_count
FROM your_table
You can extend that for other combinations of null/not null
您可以将其扩展为空/非空的其他组合
回答by tbone
select sum(decode(a,null,0,1)) as "NotNullCount", sum(decode(a,null,1,0)) as "NullCount"
from myTable;
Repeat for as many fields as you like.
重复任意数量的字段。
回答by Vladimir Kishlaly
It can be accomplished in Oracle just in 1 row:
它可以在 Oracle 中仅在 1 行中完成：
SELECT COUNT(NVL(potential_null_column, 0)) FROM table;
Function NVLchecks if first argument is null and treats it as value from second argument.
函数NVL检查第一个参数是否为空并将其视为来自第二个参数的值。
回答by ds27680
One way to do it would be:
一种方法是：
select count(*) from table group by nvl2(a, 0, 1), nvl2(b, 0, 1) having nvl2(a,0,1) = nvl2(b,0,1);
回答by Lorin Davis
This worked well for me for counting getting the total count for blank cells on a group of columns in a table in oracle: I added the trim to count empty spaces as null
这对我来说很有效，用于计算 oracle 表中一组列上空白单元格的总数：我添加了修剪以将空白空间计为空
SELECT (sum(case
when trim(a) is null Then 1
else 0
end)) +
(sum(case
when trim(b) is null
else 0
end)) +
(sum(case
when trim(c) is null
else 0
end)) as NullCount
FROM your_table
Hope this helps
希望这可以帮助
Cheers.
干杯。
