我有一张桌子
create table us
(
a number
);
现在我有如下数据:
a
1
2
3
4
null
null
null
8
9
现在我需要一个查询来计算列a中的空值和非空值
我有一张桌子
create table us
(
a number
);
现在我有如下数据:
a
1
2
3
4
null
null
null
8
9
现在我需要一个查询来计算列a中的空值和非空值
当前回答
下面是一个在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
其他回答
下面是一个在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 sum(case when a is null then 0 else 1 end) as count_notnull,
sum(case when a is null then 1 else 0 end) as count_null
from tab
group by a
如果是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
以防你想把它记录在一条记录里:
select
(select count(*) from tbl where colName is null) Nulls,
(select count(*) from tbl where colName is not null) NonNulls
;-)
Try
SELECT
SUM(ISNULL(a)) AS all_null,
SUM(!ISNULL(a)) AS all_not_null
FROM us;
简单!