我有一张桌子

create table us
(
 a number
);

现在我有如下数据:

a
1
2
3
4
null
null
null
8
9

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


当前回答

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

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

其他回答

用于计数非空值

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

用于计算空值

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

我有一个类似的问题:要计算所有不同的值,空值也算作1。简单的计数在这种情况下不起作用,因为它不考虑空值。

下面是一个适用于SQL的代码片段,不涉及选择新值。 基本上,执行distinct后,还使用row_number()函数返回新列(n)中的行号,然后对该列执行计数:

SELECT COUNT(n)
FROM (
    SELECT *, row_number() OVER (ORDER BY [MyColumn] ASC) n
    FROM (
        SELECT DISTINCT [MyColumn]
                    FROM [MyTable]
        ) items  
) distinctItems

a为空的元素个数:

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

a不为空的元素个数:

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

这在T-SQL中有效。如果你只是计算一些东西的数量,你想包括空值,使用COALESCE而不是case。

IF OBJECT_ID('tempdb..#us') IS NOT NULL
    DROP TABLE #us

CREATE TABLE #us
    (
    a INT NULL
    );

INSERT INTO #us VALUES (1),(2),(3),(4),(NULL),(NULL),(NULL),(8),(9)

SELECT * FROM #us

SELECT CASE WHEN a IS NULL THEN 'NULL' ELSE 'NON-NULL' END AS 'NULL?',
        COUNT(CASE WHEN a IS NULL THEN 'NULL' ELSE 'NON-NULL' END) AS 'Count'
    FROM #us
    GROUP BY CASE WHEN a IS NULL THEN 'NULL' ELSE 'NON-NULL' END

SELECT COALESCE(CAST(a AS NVARCHAR),'NULL') AS a,
        COUNT(COALESCE(CAST(a AS NVARCHAR),'NULL')) AS 'Count'
    FROM #us
    GROUP BY COALESCE(CAST(a AS NVARCHAR),'NULL')

如果是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