如何在 Oracle/PLSQL 中只计算 NULL 值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2841551/
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 can I count only NULL values in Oracle/PLSQL?
提问by Dan F.
How can I count only NULL values in Oracle/PLSQL?
如何在 Oracle/PLSQL 中只计算 NULL 值？
I want to count only the null values. Is there a function that does that?
我只想计算空值。有没有这样做的功能？
回答by mdma
I don't know Oracle specifally, but ANSI SQL, COUNT(rowName)does not count NULLvalues, but COUNT(*)does. So you can write
我不知道 Oracle 具体，但 ANSI SQL，COUNT(rowName)不计算NULL值，但COUNT(*)会。所以你可以写
SELECT COUNT(*) FROM YourTable WHERE YourColumn IS NULL
which counts the rows in YourTable that have YourColumn set to NULL.
它计算 YourTable 中将 YourColumn 设置为 NULL 的行。
回答by Gary Myers
As an alternative to mdma's response. If you don't want to put a filter in the where you can
作为 mdma 回应的替代方案。如果您不想将过滤器放在可以使用的位置
SELECT COUNT(case when xxx IS NULL THEN 1 end) cnt_xxx_null
FROM table
回答by Ian Carpenter
The Oracle documentationstates that:
Oracle文档指出：
All aggregate functions except COUNT(*) and GROUPING ignore nulls. You can use the NVL function in the argument to an aggregate function to substitute a value for a null.
除 COUNT(*) 和 GROUPING 之外的所有聚合函数都忽略空值。您可以在聚合函数的参数中使用 NVL 函数来替换空值。
As an example, using the scott schema:
例如，使用 scott 模式：
SQL> select empno, sal, comm
2 from emp;
EMPNO SAL COMM
---------- ---------- ----------
7369 800
7499 1600 300
7521 1250 500
7566 2975
7654 1250 1400
7698 2850
7782 2450
7788 3000
7839 5000
7844 1500 0
7876 1100
7900 950
7902 3000
7934 1300
14 rows selected.
You can see that the Comm column has 4 known values (i.e. Not null) and 10 unknown values (i.e. Null)
可以看到 Comm 列有 4 个已知值（即 Not null）和 10 个未知值（即 Null）
As count(your_column_name)ignores nulls you need to substitute the unknown values for something you can refer to. This can be achieved using the NVLfunction.
由于count(your_column_name)忽略空值，您需要将未知值替换为您可以参考的内容。这可以使用NVL功能来实现。
SQL> select count(nvl(comm, -1)) "number of null values"
2 from emp
3 where nvl(comm, -1) = -1;
number of null values
---------------------
10
I have used the value "-1" as the "alias" for my null values because I know that "-1" is not an existing value within the comm column.
我使用值“-1”作为我的空值的“别名”，因为我知道“-1”不是通信列中的现有值。
EDIT:
编辑：
Following Rob's suggestion. It is possible to remove the where clause from the above example and use the NVL2function as shown below:
遵循罗布的建议。可以从上面的示例中删除 where 子句并使用NVL2函数，如下所示：
SQL> select count(nvl2(comm,null,-1)) "number of null values"
2 from emp
3 /
number of null values
---------------------
10
回答by Imran
If you wants to count other values too with nullthen use of COALESCEfunction will improves execution time
如果您也想用null计算其他值，那么使用COALESCE函数将提高执行时间
Oracle Differences between NVL and Coalesce
SELECT COUNT(COALESCE( _COLUMN, 1)) AS CNT FROM _TABLE
回答by armagedescu
I might try to inverse the null, see results
我可能会尝试反转 null，看看结果
SELECT
COUNT(DECODE(YourField, null, 1, null)) Nulls,
count(*) Everything,
COUNT(YourField) NotNulls
FROM YourTable
Everything should equal nulls + notnulls
一切都应该等于 nulls + notnulls
回答by mikcutu
select count(nvl(values, 0)) from emp where values is null;
回答by vivek
Function:
功能：
create or replace function xxhrs_fb_count_null
return number
as
l_count_null number;
begin
select count(*) into l_count_null from emp where comm is null;
return l_count_null;
end;
Usage:
用法：
select xxhrs_fb_count_null from dual
