我有一张桌子

create table us
(
 a number
);

现在我有如下数据:

a
1
2
3
4
null
null
null
8
9

现在我需要一个查询来计算列a中的空值和非空值


当前回答

a为空的元素个数:

select count(a) from us where a is null;

a不为空的元素个数:

select count(a) from us where a is not null;

其他回答

下面是一个在Oracle上运行的快速而简单的版本:

select sum(case a when null then 1 else 0) "Null values",
       sum(case a when null then 0 else 1) "Non-null values"
from us
SELECT
    ALL_VALUES
    ,COUNT(ALL_VALUES)
FROM(
        SELECT 
        NVL2(A,'NOT NULL','NULL') AS ALL_VALUES 
        ,NVL(A,0)
        FROM US
)
GROUP BY ALL_VALUES

正如我理解你的查询,你只需要运行这个脚本并获得Total Null,Total NotNull行,

select count(*) - count(a) as 'Null', count(a) as 'Not Null' from us;

以防你想把它记录在一条记录里:

select 
  (select count(*) from tbl where colName is null) Nulls,
  (select count(*) from tbl where colName is not null) NonNulls 

;-)

select count(isnull(NullableColumn,-1))