本文介绍了计算SQL中每列中NULL值的数量的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试编写一个脚本,该脚本将显示每列中非空值的数量以及表中的总行数.
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值的数量
发布评论