计算SQL中每列中NULL值的数量 [英] Count number of NULL values in each column in SQL

问题描述

我正在尝试编写一个脚本，该脚本将显示每列中非空值的数量以及表中的总行数.

I am trying to write a script that will show the number of non-null values in each column as well as the total number of rows in the table.

我找到了几种方法来做到这一点:

I have found a couple ways to do this:

SELECT sum(case my_column when null then 1 else 0) "Null Values",
   sum(case my_column when null then 0 else 1) "Non-Null Values"
FROM my_table;

和

SELECT count(*) FROM my_table WHERE my_column IS NULL 
UNION ALL
SELECT count(*) FROM my_table WHERE my_column IS NOT NULL

但是这些需要我手动输入每个列名.有没有办法对每列执行此操作而不列出它们?

But these require me to type in each column name manually. Is there a way to perform this action for each column without listing them?

推荐答案

正如 Paolo 所说，但这里有一个例子:

As Paolo said, but here is an example:

DECLARE @TableName VARCHAR(512) = 'invoiceTbl';
DECLARE @SQL VARCHAR(1024);
WITH SQLText AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY c.Name) AS RowNum,
        'SELECT ''' + c.name + ''', SUM(CASE WHEN ' + c.Name + ' IS NULL THEN 1 ELSE 0 END) AS NullValues FROM ' + @TableName AS SQLRow
    FROM 
        sys.tables t 
        INNER JOIN sys.columns c ON c.object_id = t.object_id
    WHERE 
        t.name = @TableName),
Recur AS (
    SELECT
        RowNum,
        CONVERT(VARCHAR(MAX), SQLRow) AS SQLRow
    FROM
        SQLText
    WHERE
        RowNum = 1
    UNION ALL
    SELECT
        t.RowNum,
        CONVERT(VARCHAR(MAX), r.SQLRow + ' UNION ALL ' + t.SQLRow)
    FROM
        SQLText t
        INNER JOIN Recur r ON t.RowNum = r.RowNum + 1
    )
SELECT @SQL = SQLRow FROM Recur WHERE RowNum = (SELECT MAX(RowNum) FROM Recur);
EXEC(@SQL);

这篇关于计算SQL中每列中NULL值的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！