在计算SUM之前筛选列(错误:将数据类型varchar转换为int时出错)

Question

我需要计算最小值/最大值等。a表中列的值。所以我想过滤INFORMATION_SCHEMA中的列，只得到数字列。

以下查询返回一个错误：将数据类型varchar转换为int时出错。我不知道如何修复它。

SELECT @vQuery = 
'SELECT  '''+TABLE_NAME+''' AS TableName 
, '''+COLUMN_NAME+''' AS ColumnName
, '''+DATA_TYPE+''' AS DataType
, MIN(TRY_CAST(TRY_CAST(['+COLUMN_NAME+'] AS VARCHAR(MAX)) AS NUMERIC(30,4))) AS MinValue
, MAX(TRY_CAST(TRY_CAST(['+COLUMN_NAME+'] AS VARCHAR(MAX)) AS NUMERIC(30,4))) AS MaxValue
, AVG(TRY_CAST(TRY_CAST(['+COLUMN_NAME+'] AS VARCHAR(MAX)) AS NUMERIC(30,4))) AS AvgValue
, STDEV(TRY_CAST(TRY_CAST(['+COLUMN_NAME+'] AS VARCHAR(MAX)) AS NUMERIC(30,4))) AS StandardDeviation
, SUM(TRY_CAST(TRY_CAST(['+COLUMN_NAME+'] AS VARCHAR(MAX)) AS NUMERIC(30,4))) AS TotalSum
FROM '+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)+';'+ CHAR(10)         
FROM 
(SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @Schema
AND TABLE_NAME = @Table
AND DATA_TYPE IN ('BIGINT','NUMERIC','SMALLINT','DECIMAL','SMALLMONEY','INTEGER','INT','TINYINT','MONEY','FLOAT','REAL')) t

Answer 1

这对于注释来说有点长：

您的查询在此处似乎有效。

如果您没有使用try_()函数，那么当运行查询时可能会出现问题，而不是在生成查询时。问题是指数表示法。例如：

select convert(varchar(255), convert(float, 1234556678990.0))

退货：

'1.23456e+012'

并且该值不能转换为数值。

我认为在转换为数字之前先转换为字符串没有任何好处，所以我会放弃这些转换。但是，对于try_cast()，这不应该是一个问题。

事实上，您正在为每种类型生成单独的查询。我认为没有必要进行任何转换。如果需要，查询可以返回不同的类型。如果使用UNION ALL连接查询，则需要关注类型。

注意：您还应该在列名上使用QUOTENAME()。它可以防止SQL注入，但当您从INFORMATION_SCHEMA表中进行选择时，这似乎是一个不太可能的问题(必须有人创建非常奇怪的表/列名(。但是，它也处理不一致的列名，例如带有空格的列名。