用于计数非空值

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

用于计算空值

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

如果你正在使用MS Sql Server…

SELECT COUNT(0) AS 'Null_ColumnA_Records',
(
    SELECT COUNT(0)
    FROM your_table
    WHERE ColumnA IS NOT NULL
) AS 'NOT_Null_ColumnA_Records'
FROM your_table
WHERE ColumnA IS NULL;

我不建议你这么做……但这里你有它(在同一张表中的结果)

对于非空值

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

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

以防你想把它记录在一条记录里:

select 
  (select count(*) from tbl where colName is null) Nulls,
  (select count(*) from tbl where colName is not null) NonNulls 

;-)

在阿尔贝托的基础上，我添加了汇总。

 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;

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