被零除在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。然而，我认为只使用NULLIF()更容易，这是一个ANSI标准函数。而不是像这样的逻辑：

(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],

Answer 2

而不是使用

WHERE A.[HU_VACANT] > 0

您应该过滤掉根级别的记录

having SUM([ESTIMATE_1]) > 0

您也可以使用nullif函数，但这将导致在您有零的地方生成NULL