我正着写一个脚本,它将显示每列中非空值的数量和表中的总行数。7 T+ G" V) v! {0 b; o' O% c
我发现有几种方法可以做到这一点:( Y( _9 F+ [7 x) L
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;和 l! }; c7 i* Z' V
SELECT count(*) FROM my_table WHERE my_column IS NULL UNION ALLSELECT count(*) FROM my_table WHERE my_column IS NOT NULL但这些要求我手动输入每个列的名称。有没有办法在不列出的情况下执行每个列? # ?' v% f+ E7 X3 O k6 q 6 m6 b& q' L& w$ r5 _解决方案: 5 J4 Y: u, o, V# D0 B) A: M
正如Paolo但这是一个例子: ) @8 u1 E/ _. Q& \! }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);