动态SQL查询以查找表的所有列中缺失值的计数

Question

动态SQL查询以查找表的所有列中缺失值的计数

3

我可以帮助你翻译这段有关IT技术的内容。该内容需要动态SQL查询来查找表中每个列的缺失值数量。由于该表包含40个列，为每个列编写缺失计数很冗长，因此我们能否动态地实现呢？我尝试编写以下动态查询，但出现了错误：

必须声明标量变量“@sql”

查询：

Declare @sql nvarchar(max)  
Declare @columnlist nvarchar(max)   
Declare @FieldName nvarchar(max)    

set @columnlist = 'Column 1,Column2 ,Column3 ,Column4 ,Column5 ,Column6 ,Column7 ,Column8 ,Column9 ,Column10 ,Column11 ,Column12,Column13'  

set @FieldName = 'Column 1,Column2 ,Column3 ,Column4 ,Column5 ,Column6 ,Column7 ,Column8 ,Column9 ,Column10 ,Column11 ,Column12,Column13'

set @sql = 'SELECT COUNT(*)-COUNT(' + @columnlist + ') as '+ @FieldName +'from table_name' 

exec (@sql)

- Promila

1

您打错了。第二个查询 set @FieldName 中没有起始单引号。 - Nisarg Shah

尝试使用双引号 "" 在 select 查询中。 - Bhargav Chudasama

@NisargShah 我已经更新了，但仍然出现相同的错误。 - Promila

你能分享具体的错误信息吗？ - Bhargav Chudasama

1

我认为将这40个表达式复制粘贴下来会比写两次字段列表，然后构建代码将列列表字符串转换为某种表达式更快，以便能够动态构建表达式列表。 - Wolfgang Kais

显示剩余6条评论

2个回答

0

试试这个

IF OBJECT_ID('TempDb..#MissingList') IS NOT NULL
    DROP TABLE #MissingList

CREATE TABLE #MissingList
(
    SeqNo INT IDENTITY(1,1),
    TABLE_NAME VARCHAR(255),
    COLUMN_NAME VARCHAR(255),
    MissingCount INT DEFAULT(0)
)

DECLARE @SQL NVARCHAR(MAX)

SELECT
    @SQL = REPLACE(REPLACE(L.List,'&#x0D',''),';','')
    FROM
    (
        SELECT 
            '
            INSERT INTO #MissingList(TABLE_NAME,COLUMN_NAME,MissingCount)
            SELECT TABLE_NAME = ''['+LTRIM(RTRIM(TABLE_SCHEMA))+'].['+LTRIM(RTRIM(TABLE_NAME))+']'',COLUMN_NAME = '''+LTRIM(RTRIM(COLUMN_NAME))+''',MissingCount = COUNT(1) - COUNT(['+LTRIM(RTRIM(COLUMN_NAME))+'])
            FROM ['+LTRIM(RTRIM(TABLE_SCHEMA))+'].['+LTRIM(RTRIM(TABLE_NAME))+']' AS [text()]
            FROM INFORMATION_SCHEMA.COLUMNS
            FOR XML PATH('')
    )L(LIST)

EXEC(@SQL)

SELECT
    *
    FROM #MissingList

- Jayasurya Satheesh

1

使用 '[ + @Variable + ']' 无法阻止注入，也无法处理命名不当的对象（确实存在带有右括号 ] 的对象，相信我）。QUOTENAME 是一个更好的选择。 - Thom A

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Wolfgang Kais · Accepted Answer

试试这个：

DECLARE @sql nvarchar(max) = N'SELECT';
DECLARE @table_name nvarchar(256) = N'YourTableName'

SELECT @sql = @sql + ' COUNT(*)-COUNT(' + QUOTENAME(COLUMN_NAME) + ') as '+ QUOTENAME(COLUMN_NAME) + N','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = N'dbo'
  AND TABLE_NAME = @table_name

SET @sql = LEFT(@sql, LEN(@sql) - 1) + ' from ' + @table_name

EXEC (@sql)

新增

好的，下面是一个示例，说明如何使用类似的方法来计算每一列中至少有一个重复值的数量：

DECLARE @sql nvarchar(max) = N'WITH duplicates AS (SELECT';
DECLARE @table_name nvarchar(256) = N'YourTableName';

SELECT @sql = @sql 
  + N' CASE WHEN COUNT(' + QUOTENAME(COLUMN_NAME) 
  + N') OVER (PARTITION BY ' + QUOTENAME(COLUMN_NAME) 
  + N') > 1 THEN ' + QUOTENAME(COLUMN_NAME)  
  + N' END as '+ QUOTENAME(COLUMN_NAME) + N','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = N'dbo'
  AND TABLE_NAME = @table_name;

SET @sql = LEFT(@sql, LEN(@sql) - 1) + ' from ' + @table_name + ') SELECT';

SELECT @sql = @sql 
  + N' COUNT(DISTINCT ' + QUOTENAME(COLUMN_NAME) 
  + N') as '+ QUOTENAME(COLUMN_NAME) + N','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = N'dbo'
  AND TABLE_NAME = @table_name;

SET @sql = LEFT(@sql, LEN(@sql) - 1) + ' from duplicates';

EXEC (@sql);

您可以看到它使用了2个SELECT语句，第一个创建了一个CTE，用NULL替换了所有没有重复值的值：

CASE WHEN COUNT([C1]) OVER (PARTITION BY [C1]) > 1 THEN [C1] END as [C1]

第二种方法使用COUNT DISTINCT来计算剩下的内容。这样做，NULL值将不会被计算在内。