我有一张桌子

create table us
(
 a number
);

现在我有如下数据:

a
1
2
3
4
null
null
null
8
9

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


当前回答

试试这个. .

SELECT CASE 
         WHEN a IS NULL THEN 'Null' 
         ELSE 'Not Null' 
       END a, 
       Count(1) 
FROM   us 
GROUP  BY CASE 
            WHEN a IS NULL THEN 'Null' 
            ELSE 'Not Null' 
          END 

其他回答

下面是一个在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

如果是mysql,你可以尝试这样做。

select 
   (select count(*) from TABLENAME WHERE a = 'null') as total_null, 
   (select count(*) from TABLENAME WHERE a != 'null') as total_not_null
FROM TABLENAME

使用ISNULL嵌入函数。


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

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

a为空的元素个数:

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

a不为空的元素个数:

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