我有一张桌子

create table us
(
 a number
);

现在我有如下数据:

a
1
2
3
4
null
null
null
8
9

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


当前回答

对于非空值

select count(a)
from us

null值

select count(*)
from us

minus 

select count(a)
from us

因此

SELECT COUNT(A) NOT_NULLS
FROM US

UNION

SELECT COUNT(*) - COUNT(A) NULLS
FROM US

应该做这项工作

更好的是列标题是正确的。

SELECT COUNT(A) NOT_NULL, COUNT(*) - COUNT(A) NULLS
FROM US

在我的系统上进行的一些测试中,需要进行全表扫描。

其他回答

所有的答案要么是错误的,要么是非常过时的。

执行此查询的简单而正确的方法是使用COUNT_IF函数。

SELECT
  COUNT_IF(a IS NULL) AS nulls,
  COUNT_IF(a IS NOT NULL) AS not_nulls
FROM
  us

Try

SELECT 
   SUM(ISNULL(a)) AS all_null,
   SUM(!ISNULL(a)) AS all_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;
SELECT SUM(NULLs) AS 'NULLS', SUM(NOTNULLs) AS 'NOTNULLs' FROM 
    (select count(*) AS 'NULLs', 0 as 'NOTNULLs' FROM us WHERE a is null
    UNION select 0 as 'NULLs', count(*) AS 'NOTNULLs' FROM us WHERE a is not null) AS x

这很糟糕,但它将返回一个带有2个cols的记录,指示null和非null的计数。

这里有两种解决方案:

Select count(columnname) as countofNotNulls, count(isnull(columnname,1))-count(columnname) AS Countofnulls from table name

OR

Select count(columnname) as countofNotNulls, count(*)-count(columnname) AS Countofnulls from table name