sql - 查询中更清晰的案例陈述？

Question

我正在做一个插入，我插入的值之一计算如下:

SUM(Value1)/(
    CASE SUM(
        CASE Value2 > 0 
        THEN 1
        ELSE 0 
        END
    ) = 0 
    THEN 1 
    ELSE SUM(
        CASE Value2 > 0 
        THEN 1 
        ELSE 0 
        END
    )
)

内部 CASE 部分重复了两次，最好为该值设置一个别名或变量，然后改用该别名或变量。

Answer 1

CTE 答案...

WITH
  step1 AS
(
  SELECT
    SUM(value1)                            AS interimValue1
    SUM(CASE value2 > 0 THEN 1 ELSE 0 END) AS interimValue2,
    <other fields>
  FROM
    <a query>
)
SELECT
  interimValue / CASE interimValue2 = 0 THEN 1 ELSE interimValue2 END
FROM
  step1

子查询版本...

SELECT
  interimValue / CASE interimValue2 = 0 THEN 1 ELSE interimValue2 END
FROM
(
  SELECT
    SUM(value1)                            AS interimValue1
    SUM(CASE value2 > 0 THEN 1 ELSE 0 END) AS interimValue2,
    <other fields>
  FROM
    <a query>
)
  AS step1

或者，更具体到您发布的 SQL，而不是对一般情况的回答...

SUM(value1) / COALESCE(SUM(CASE WHEN value2 > 0 THEN 1 ELSE NULL END), 1)

(在返回 0 的地方返回 NULL 允许 COALESCE 将 NULL 替换为 1。)