如何按valuation_type条件选取对应价格？SQL查询逻辑调整需求

需求说明 #

需根据表中valuation_type的条件选取对应价格，表结构如下：

执行逻辑 #

• 若valuation_type为NULL，直接取该行的price；
• 若valuation_type不为NULL，取product_item_code、distribution_channel、sap_no_shipto分组中的最高price。

表唯一约束 #

表存在组合唯一约束：product_item_code + distribution_channel + sap_no_shipto + valuation_type

现有SQL问题 #

现有子查询仅能选取分组内的最大值，会忽略valuation_type为NULL的情况，无法满足第一个条件：

SELECT MAX(psp.price)
FROM product_special_prices psp
WHERE
  psp.product_item_code = p.item_code AND
  psp.distribution_channel = st.distribution_channel AND
  psp.sap_no_shipto = st.sap_no_shipto
GROUP BY (
  psp.product_item_code,
  psp.distribution_channel,
  psp.sap_no_shipto
)

调整方案 #

方案一：分情况判断（兼容多数数据库） #

通过EXISTS先判断是否存在valuation_type为NULL的记录，存在则直接取该价格，否则取非NULL记录的最大值：

SELECT 
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM product_special_prices psp_null
            WHERE psp_null.product_item_code = p.item_code 
              AND psp_null.distribution_channel = st.distribution_channel 
              AND psp_null.sap_no_shipto = st.sap_no_shipto
              AND psp_null.valuation_type IS NULL
        ) THEN (
            SELECT psp_null.price
            FROM product_special_prices psp_null
            WHERE psp_null.product_item_code = p.item_code 
              AND psp_null.distribution_channel = st.distribution_channel 
              AND psp_null.sap_no_shipto = st.sap_no_shipto
              AND psp_null.valuation_type IS NULL
        )
        ELSE (
            SELECT MAX(psp.price)
            FROM product_special_prices psp
            WHERE psp.product_item_code = p.item_code 
              AND psp.distribution_channel = st.distribution_channel 
              AND psp.sap_no_shipto = st.sap_no_shipto
              AND psp.valuation_type IS NOT NULL
        )
    END AS target_price

方案二：窗口函数实现（适合支持窗口函数的数据库） #

利用FIRST_VALUE窗口函数，优先将valuation_type为NULL的记录排在最前面，再按价格降序排序，直接取分组后的第一个值：

SELECT DISTINCT
    FIRST_VALUE(psp.price) OVER (
        PARTITION BY psp.product_item_code, psp.distribution_channel, psp.sap_no_shipto
        ORDER BY 
            CASE WHEN psp.valuation_type IS NULL THEN 0 ELSE 1 END,
            psp.price DESC
    ) AS target_price
FROM product_special_prices psp
WHERE psp.product_item_code = p.item_code 
  AND psp.distribution_channel = st.distribution_channel 
  AND psp.sap_no_shipto = st.sap_no_shipto

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