sql - 即使我排除了零情况，SQL 中除以零错误？

Question

这是我一直尝试运行的代码:

SELECT C.* FROM
(SELECT 
B.[OUTSIDE_ROW],
B.[INSIDE_ROW],
B.[r_HU_vac_ns],
B.[r_HU_vac_ns_MOE],
CASE WHEN B.[r_HU_vac_ns] = 0 THEN 999 ELSE B.[r_HU_vac_ns_MOE]/B.[r_HU_vac_ns] END AS [PCT]
FROM
(SELECT 
A.[OUTSIDE_ROW], 
A.[INSIDE_ROW], 
(A.[HU_VACANT] - A.[HU_VACANT_SEASONAL_RECREATIONAL])/A.[HU_VACANT] AS [r_HU_vac_ns], 
(1/A.[HU_VACANT]) * POWER( 
CASE WHEN ((A.[HU_VACANT] - A.[HU_VACANT_SEASONAL_RECREATIONAL])/A.[HU_VACANT]) * POWER(A.[HU_VACANT_MOE], 2) < POWER(A.[HU_VACANT_MOE], 2) +     POWER(A.[HU_VACANT_SEASONAL_RECREATIONAL_MOE], 2) THEN POWER(A.[HU_VACANT_MOE],     2) + POWER(A.[HU_VACANT_SEASONAL_RECREATIONAL_MOE], 2) - (((A.[HU_VACANT] - A.    [HU_vACANT_SEASONAL_RECREATIONAL])/A.[HU_VACANT]) * POWER(A.[HU_VACANT_MOE], 2))
ELSE POWER(A.[HU_VACANT_MOE], 2) + POWER(A.    [HU_VACANT_SEASONAL_RECREATIONAL_MOE], 2) + (((A.[HU_VACANT] - A.    [HU_vACANT_SEASONAL_RECREATIONAL])/A.[HU_VACANT]) * POWER(A.[HU_VACANT_MOE], 2)) END, 0.5) AS [r_HU_vac_ns_MOE]
FROM
(SELECT 
[OUTSIDE_ROW], 
[INSIDE_ROW], 
SUM([ESTIMATE_1]) AS [HU_VACANT], 
POWER(SUM(POWER([MOE_1], 2)), 0.5) AS [HU_VACANT_MOE],
SUM([ESTIMATE_2]) AS [HU_VACANT_SEASONAL_RECREATIONAL],
POWER(SUM(POWER([MOE_2], 2)), 0.5) AS [HU_VACANT_SEASONAL_RECREATIONAL_MOE]
FROM #TEST_TABLE
GROUP BY [OUTSIDE_ROW], [INSIDE_ROW]) A
WHERE A.[HU_VACANT] > 0) B ) C 
WHERE C.[PCT] < 0.2

每次运行时，都会出现以下错误:

Msg 8134, Level 16, State 1, Line 533
Divide by zero error encountered.

但是，如果我去掉最后一行代码(以下 WHERE 子句)，则代码运行正常:

WHERE C.[PCT] < 0.2

只要看看我的查询，谁能告诉我我做错了什么？我以为我用下面的 CASE WHEN 语句消除了所有为零的 PCT 值，所以这个错误让我感到困惑:

 CASE WHEN B.[r_HU_vac_ns] = 0 THEN 999 ELSE B.[r_HU_vac_ns_MOE]/B.[r_HU_vac_ns] END AS [PCT]

如果有帮助，PCT 将转换为浮点。

谢谢。

Answer 1

SQL Server 保留重新安排计算的权利。这意味着SELECT 中的计算可以在发生过滤之前进行。即使过滤器位于子查询和 CTE 中也是如此。

保证计算顺序的唯一方法是CASE。不过，我认为使用 ANSI 标准函数 NULLIF() 更容易。而不是这样的逻辑:

(A.[HU_VACANT] - A.[HU_VACANT_SEASONAL_RECREATIONAL])/A.[HU_VACANT] AS [r_HU_vac_ns],

做:

(A.[HU_VACANT] - A.[HU_VACANT_SEASONAL_RECREATIONAL])/NULLIF(A.[HU_VACANT], 0) AS [r_HU_vac_ns],