基于层级规则的定价SQL查询开发需求与问题咨询

针对你提出的定价规则，我们可以通过CTE（公共表表达式）先预处理lvlholder客户下的最新价格记录，再通过关联查询匹配到主查询中的对应价格，完整SQL如下：

WITH LvlHolderPrices AS (
    SELECT 
        itemcode,
        uom,
        blvl,
        price,
        -- 按商品、单位、级别分组，取每组中最新生效的价格
        ROW_NUMBER() OVER (PARTITION BY itemcode, uom, blvl ORDER BY start_date DESC) AS rn
    FROM [test].[testtable].[pricing]
    WHERE start_cust_or_group = 'lvlholder'
      AND RowDeleted = 0
)
SELECT 
    p.itemcode,
    p.uom,
    p.trtype, 
    p.alvl,
    p.blvl,
    p.start_date,
    CASE 
        -- 规则1：trtype为Quot时直接取当前price
        WHEN p.trtype = 'Quot' THEN p.price 
        -- 规则2：Alvl>0时，匹配lvlholder中同级别（Blvl=Alvl）的最新价格
        WHEN p.alvl > 0 THEN lhp1.price
        -- 规则3：trtype为空且Alvl=0时，取lvlholder中Blvl=8的最新默认价格
        WHEN p.trtype = '' AND p.alvl = 0 THEN lhp2.price
        ELSE 0 
    END AS LevelResult 
FROM [test].[testtable].[pricing] p
-- 关联规则2对应的最新级别价格
LEFT JOIN LvlHolderPrices lhp1 
    ON p.itemcode = lhp1.itemcode 
    AND p.uom = lhp1.uom 
    AND p.alvl = lhp1.blvl 
    AND lhp1.rn = 1
-- 关联规则3对应的默认级别价格
LEFT JOIN LvlHolderPrices lhp2 
    ON p.itemcode = lhp2.itemcode 
    AND p.uom = lhp2.uom 
    AND lhp2.blvl = 8 
    AND lhp2.rn = 1
WHERE p.start_cust_or_group = 'test'  
   AND p.RowDeleted = 0
   AND p.item_or_pricecode IN ('ABC1234','X123456','BR23456','CX23456')
ORDER BY p.start_date DESC

关键说明： #

• LvlHolderPrices CTE：通过ROW_NUMBER()窗口函数，给lvlholder客户下的每个商品、单位、级别组合分配序号，最新的生效日期（start_date）序号为1，确保我们只取每组的最新价格。
• 两次LEFT JOIN：分别匹配规则2和规则3对应的价格，避免因某级别无价格导致主查询记录被过滤。
• CASE语句严格遵循你定义的三个优先级规则，确保逻辑顺序正确。

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