计算SQL中每列中NULL值的数量
我正着写一个脚本，它将显示每列中非空值的数量和表中的总行数。我发现有几种方法可以做到这一点：
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 ALLSELECT count(*) FROM my_table WHERE my_column IS NOT NULL但这些要求我手动输入每个列的名称。有没有办法在不列出的情况下执行每个列？
解决方案:
正如Paolo但这是一个例子：
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 ASSELECT 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);
页:
[1]