sql-server-2008 - CTE 返回错误

Question

我编写了一个 CTE 来从数据集中删除非数值，然后获取一个范围内的数值计数。

WITH    dtr
      AS ( SELECT   resultlevel r
           FROM     dbo.Result
           WHERE    DrugID = 'AMP'
                    AND ISNUMERIC(ResultLevel) = 1
                    AND AuditStamp > '1/1/2016'
                    AND DeleteFlag = 0
         )
SELECT  COUNT(*)
FROM    dtr
WHERE   CONVERT(INT, r) BETWEEN 50 AND 75

这会在 SMS 中返回一个错误

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'PND            ' to data type int.

如果 CTE 中没有 'dtr' 查询，这个错误是完全可能的。

当我重写它时，不是 CTR，而是 TEMP 表，它起作用了。

SELECT  resultlevel r
INTO    #d
FROM    dbo.Result
WHERE   DrugID = 'AMP'
        AND ISNUMERIC(ResultLevel) = 1
        AND AuditStamp > '1/1/2016'
        AND DeleteFlag = 0

SELECT  COUNT(*)
FROM    #d
WHERE   CONVERT(INT, r) BETWEEN 50 AND 75

所以我的问题是为什么？我一直认为 CTE 就像创建一个 TEMP 表。

测试数据

if object_id('tempdb..#temp') is not null drop table #temp

create table #temp (result char(5)) 
insert into #temp (result) values 
('1'),('A'),('>2'),('PEN ') ,('@3'),('-2'),('-33') 


;with isnum AS ( 
SELECT result 
FROM #temp 
WHERE ISNUMERIC(result) = 1)

--Selecting from the CTE yields 1, -2, and -33 all of which can be converted to INT
--Running the query with the where clause causes the conversion error
SELECT 
    result, 
    ISNUMERIC(result)
FROM isnum
--WHERE CONVERT(INT,result) > 1

Answer 1

在 SQL Server 中有 Logical Processing Order of the SELECT statement , 它决定了一步中定义的对象何时可用于后续步骤中的子句:

1. 来自
2. 开启
3. 加入
4. 哪里
5. 分组依据
6. 使用 CUBE 或 ROLLUP
7. 拥有
8. 选择
9. 不同
10. 订购方式
11. 顶部

这就是您的查询将如何处理，您的查询看起来非常好。但有时，SQL Server 决定不遵循此顺序以优化您的查询。

在您的情况下，SQL Server 可能只是将您的查询转换为另一个查询并执行 convert 函数，然后再应用 where isnumeric 过滤。

如果我们让您的查询稍微复杂一点(但仍然给出相同的结果)，SQL Server 这次将正确执行代码:

;with isnum AS ( 
    SELECT result
    FROM #temp 
    WHERE ISNUMERIC(result) = 1
    GROUP BY result
    HAVING MAX(result) = result
)
SELECT 
    result, 
    ISNUMERIC(result)
FROM isnum
WHERE CONVERT(INT,result) > 1;

在你的情况下(这就是我在不同类型存储在一列中的情况下所做的)，你可以简单地使用 TRY_CONVERT功能:

;with isnum AS ( 
SELECT result 
FROM #temp 
WHERE ISNUMERIC(result) = 1)

SELECT 
    result, 
    ISNUMERIC(result)
FROM isnum
WHERE TRY_CONVERT(INT, result) > 1