我有一张桌子

create table us
(
 a number
);

现在我有如下数据:

a
1
2
3
4
null
null
null
8
9

现在我需要一个查询来计算列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 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
select count(isnull(NullableColumn,-1))

试试这个. .

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 

这在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')

这适用于Oracle和SQL Server(你可能可以让它在其他RDBMS上工作):

select sum(case when a is null then 1 else 0 end) count_nulls
     , count(a) count_not_nulls 
  from us;

Or:

select count(*) - count(a), count(a) from us;