sql - 将数据类型 nvarchar 转换为数字时出错 - SQL Server

Question

我正在尝试取数据库中一列的平均值。该列为 AMOUNT，存储为 NVARCHAR(300),null。

当我尝试将其转换为数值时，出现以下错误:

Msg 8114, Level 16, State 5, Line 1
Error converting datatype NVARCHAR to NUMBER

这就是我现在所拥有的。

SELECT AVG(CAST(Reimbursement AS DECIMAL(18,2)) AS Amount
FROM Database
WHERE ISNUMERIC(Reimbursement) = 1 
  AND Reimbursement IS NOT NULL

Answer 1

您会认为您的代码可以工作。但是，SQL Server 不保证 WHERE 子句在 SELECT 转换发生之前过滤数据库。在我看来这是一个错误。在微软看来，这是一项优化功能。

因此，您的 WHERE 不能保证正常工作。即使使用 CTE 也无法解决问题。

最好的解决方案是 SQL Server 2012+ 中提供的 TRY_CONVERT():

SELECT AVG(TRY_CONVERT(DECIMAL(18,2), Reimbursement)) AS Amount
FROM Database
WHERE ISNUMERIC(Reimbursement) = 1 AND Reimbursement IS NOT NULL;

在早期版本中，您可以使用CASE。 CASE 确实保证子句的顺序排列，因此:

SELECT AVG(CASE WHEN ISNUMERIC(Reimbursement) = 1 AND Reimbursement IS NOT NULL
                THEN CONVERT(DECIMAL(18,2), Reimbursement))
           END)
FROM Database;

由于 AVG() 忽略 NULL 值，因此 WHERE 不是必需的，但如果您愿意，可以包含它。

最后，您可以通过使用计算列来简化代码:

alter database add Reimbursement_Value as
    (CASE WHEN ISNUMERIC(Reimbursement) = 1 AND Reimbursement IS NOT NULL
          THEN CONVERT(DECIMAL(18,2), Reimbursement))
     END);

那么你可以将代码编写为:

select avg(Reimbursement_Value)
from database
where Reimbursement_Value is not null;