我有一张桌子
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中的空值和非空值
当前回答
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的计数。
其他回答
我有一个类似的问题:要计算所有不同的值,空值也算作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
这在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')
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的计数。
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;