SQL查询求助：用CASE语句获取指定阶段金额的最值与计数

查询需求 #

• 当orl_stage_phase_nm为Closed - Won时，获取对应的orl_adnr_amt值（预期结果：1900）
• 当orl_stage_phase_nm不等于Closed - Won时，获取orl_adnr_amt的最小值（预期结果：1500）和最大值（预期结果：1900）
• 统计指定oprty_key_nbr下非空orl_adnr_amt的去重数量（预期结果：2）

原SQL查询 #

Select
A.oprty_key_nbr,
B.Closed_Won_ADNR,
B.OPEN_Phase_MIN_ADNR,
B.OPEN_Phase_MAX_ADNR,
A.#Changed_ADNR_Amts

            from(select distinct
            A.oprty_key_nbr,
            count (distinct A.orl_adnr_amt) as #Changed_ADNR_Amts

            from isell_prod_view_db.sfdc_opportunity_lifecycle A
            where A.oprty_key_nbr = '983yhjheksi9ru8jdl'
            and A.orl_adnr_amt is not null

            group by 1 ) A
            
            left join (Select distinct
                B.oprty_key_nbr,
                B.orl_adnr_amt,
                B.orl_stage_phase_nm,
                CASE WHEN B.orl_stage_phase_nm = 'Closed - Won' THEN B.orl_adnr_amt else '' end AS Closed_Won_ADNR,
                CASE WHEN B.orl_stage_phase_nm <> 'Closed - Won'THEN MIN(B.orl_adnr_amt) else '' end AS OPEN_Phase_MIN_ADNR,
                CASE WHEN B.orl_stage_phase_nm <> 'Closed - Won'THEN MAX(B.orl_adnr_amt) else '' end AS OPEN_Phase_MAX_ADNR

            from isell_prod_view_db.sfdc_opportunity_lifecycle B    
            where B.oprty_key_nbr = '983yhjheksi9ru8jdl'
            and B.orl_adnr_amt is not null
             group by 1,2,3 ) B
             
on A.oprty_key_nbr = B.oprty_key_nbr

group by 1,2,3,4,5

当前返回结果 #

期望的单行汇总结果 #

源数据 #

修正后的SQL查询 #

原查询的问题在于：子查询B中按orl_adnr_amt和orl_stage_phase_nm分组，导致生成多行数据；同时在CASE语句中嵌套聚合函数的逻辑错误，应该用聚合函数包裹CASE来实现条件统计。

修正后的SQL通过条件聚合一次性完成所有计算，无需拆分多个子查询，直接得到单行结果：

SELECT
    oprty_key_nbr,
    -- 获取Closed-Won状态下的orl_adnr_amt（假设该状态下只有唯一值）
    MAX(CASE WHEN orl_stage_phase_nm = 'Closed - Won' THEN orl_adnr_amt END) AS Closed_Won_ADNR,
    -- 计算非Closed-Won状态下的最小值
    MIN(CASE WHEN orl_stage_phase_nm <> 'Closed - Won' THEN orl_adnr_amt END) AS OPEN_Phase_MIN_ADNR,
    -- 计算非Closed-Won状态下的最大值
    MAX(CASE WHEN orl_stage_phase_nm <> 'Closed - Won' THEN orl_adnr_amt END) AS OPEN_Phase_MAX_ADNR,
    -- 统计去重的orl_adnr_amt数量
    COUNT(DISTINCT orl_adnr_amt) AS #Changed_ADNR_Amts
FROM isell_prod_view_db.sfdc_opportunity_lifecycle
WHERE oprty_key_nbr = '983yhjheksi9ru8jdl'
  AND orl_adnr_amt IS NOT NULL
GROUP BY oprty_key_nbr;

说明 #

1. 用MAX(CASE ...)获取Closed - Won状态下的orl_adnr_amt：因为该状态下只有一条有效数据，MAX/MIN都能拿到对应值，避免返回NULL。
2. 用MIN(CASE ...)和MAX(CASE ...)分别计算非Closed - Won状态下的极值，CASE会自动过滤不符合条件的行，聚合函数只对符合条件的值计算。
3. 直接在同一查询中统计去重数量，无需额外子查询，简化逻辑同时提升效率。

内容的提问来源于stack exchange，提问作者Sandi99