计算SQL中每列中的NULL值的数量

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

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

SELECT sum(case null_column为null然后是1 else 0)“Null Values”,
sum(case null_column null时然后0 else 1)“Non-Null Values”
来自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

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

解决方法

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,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);

（编辑：温州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!