使用 SQL 计算多列中的 NULL 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16528682/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Count NULL Values from multiple columns with SQL
提问by Anonymoose
I have 3 columns let say A, B, and C. I need to count the NULLvalues in each column.
我有3列让说A，B和C。我需要计算NULL每列中的值。
For example:
例如：
A | B | C
-------------
1 |NULL| 1
1 | 1 | NULL
NULL| 1 | 1
NULL|NULL| 1
Should output:
应该输出：
A | B | C
---------------
2 | 2 | 1
I've tried count, sum, sub-queries but nothing has worked for me yet. Any input would be appreciated!
我已经尝试过计数、总和、子查询，但还没有对我有用。任何输入将不胜感激！
回答by PM 77-1
SELECT COUNT(*)-COUNT(A) As A, COUNT(*)-COUNT(B) As B, COUNT(*)-COUNT(C) As C
FROM YourTable;
回答by Taryn
回答by hkravitz
For SQL SERVER you can use the following:
对于 SQL SERVER，您可以使用以下内容：
SET NOCOUNT ON
DECLARE @Schema NVARCHAR(100) = '<Your Schema>'
DECLARE @Table NVARCHAR(100) = '<Your Table>'
DECLARE @sql NVARCHAR(MAX) =''
IF OBJECT_ID ('tempdb..#Nulls') IS NOT NULL DROP TABLE #Nulls
CREATE TABLE #Nulls (TableName sysname, ColumnName sysname , ColumnPosition int ,NullCount int , NonNullCount int)
SELECT @sql += 'SELECT '''+TABLE_NAME+''' AS TableName , '''+COLUMN_NAME+''' AS ColumnName, '''+CONVERT(VARCHAR(5),ORDINAL_POSITION)+''' AS ColumnPosition, SUM(CASE WHEN '+COLUMN_NAME+' IS NULL THEN 1 ELSE 0 END) CountNulls , COUNT(' +COLUMN_NAME+') CountnonNulls FROM '+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)+';'+ CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @Schema
AND TABLE_NAME = @Table
INSERT INTO #Nulls
EXEC sp_executesql @sql
SELECT *
FROM #Nulls
DROP TABLE #Nulls
you will receive a result set with the count of Null values and non null values in each column of you table
您将收到一个结果集，其中包含表的每一列中的 Null 值和非 Null 值的计数
回答by Daniel
SELECT
(SELECT count(*) FROM my_table WHERE A is NULL) as A,
(SELECT count(*) FROM my_table WHERE B is NULL) as B,
(SELECT count(*) FROM my_table WHERE C is NULL) as C
回答by dmansfield
select sum(case when a is null then 1 else 0 end) as a_null_count
, sum(case when b is null then 1 else 0 end) as b_null_count
, sum(case when c is null then 1 else 0 end) as c_null_count
from table
select sum(case when a is null then 1 else 0 end) as a_null_count
, sum(case when b is null then 1 else 0 end) as b_null_count
, sum(case when c is null then 1 else 0 end) as c_null_count
from table
