a为空的元素个数:

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

a不为空的元素个数:

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

使用ISNULL嵌入函数。

我有一个类似的问题:要计算所有不同的值，空值也算作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

下面是一个在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 [Narrative] = CASE 
 WHEN [Narrative] IS NULL THEN 'count_total' ELSE    [Narrative] END
,[Count]=SUM([Count]) FROM (SELECT COUNT(*) [Count], 'count_nulls' AS [Narrative]  
FROM [CrmDW].[CRM].[User]  
WHERE [EmployeeID] IS NULL 
UNION
SELECT COUNT(*), 'count_not_nulls ' AS narrative 
FROM [CrmDW].[CRM].[User] 
WHERE [EmployeeID] IS NOT NULL) S 
GROUP BY [Narrative] WITH CUBE;

在我的例子中，我想要多列之间的“空分布”:

SELECT
       (CASE WHEN a IS NULL THEN 'NULL' ELSE 'NOT-NULL' END) AS a_null,
       (CASE WHEN b IS NULL THEN 'NULL' ELSE 'NOT-NULL' END) AS b_null,
       (CASE WHEN c IS NULL THEN 'NULL' ELSE 'NOT-NULL' END) AS c_null,
       ...
       count(*)
FROM us
GROUP BY 1, 2, 3,...
ORDER BY 1, 2, 3,...

根据“……它可以很容易地扩展到更多的列，只要需要