SQL查询优化：高效获取客户与商品的最低Priority定价

DevOps 小满

2026-7-2

客户定价场景的最低优先级记录筛选问题

我编写了一条包含4个UNION ALL子查询的SQL语句，每个子查询对应不同的客户定价场景，并为各场景分配了Priority值（1到4，数值越小优先级越高）。当前执行该查询时，若某客户的某商品存在场景1的定价，查询结果会同时显示该商品在场景2、3、4的记录。我尝试使用MIN(a.Priority)来筛选最低优先级记录，但未能解决问题；目前仅能通过WHERE NOT EXISTS语句实现需求，但希望找到更高效的实现方式。核心需求是返回每个CustomerNo和ItemCode对应的最低Priority的定价记录。

原查询代码如下：

select a.CustomerNo,a.CustomerName,a.ItemCode, a.ItemCodeDesc,a.ProductLine, a.PriceCode,a.CustomerPriceLevel,
    a.Price, a.Priority as [Priority]  from
    (Select a.CustomerNo,b.CustomerName,c.ItemCode, c.ItemCodeDesc,c.ProductLine, c.PriceCode, 'Item Specific' as [CustomerPriceLevel], (c.StandardUnitCost + a.DiscountMarkup1) as [Price], 1 as [Priority]
From dbo.IM_PriceCode as a
inner join dbo.AR_Customer as b on a.CustomerNo=b.CustomerNo
inner join dbo.CI_Item as c on a.ItemCode=c.ItemCode
where c.PrimaryVendorNo='0000002' and c.InactiveItem='N' and (b.UDF_NATIONALACCNT='N' or b.UDF_NATIONALACCNT='') and a.PricingMethod <>'R'

UNION ALL

    Select a.CustomerNo,c.CustomerName,b.ItemCode, b.ItemCodeDesc,a.ProductLine, a.PriceCode, a.CustomerPriceLevel, (b.StandardUnitCost + d.DiscountMarkup1) as [Price], 2 as [Priority]
From dbo.IM040_CustomerSpecialPricing as a
inner join dbo.CI_Item as b on a.PriceCode = b.PriceCode and b.ProductLine = a.ProductLine
inner join dbo.IM_PriceCode as d on b.ItemCode = d.ItemCode and d.CustomerPriceLevel = a.CustomerPriceLevel
inner join dbo.AR_Customer as c on a.CustomerNo = c.CustomerNo
where b.PrimaryVendorNo = '0000002' and b.InactiveItem = 'N' and a.ProductLine <> '' and (a.PriceCode = 'BLK' or a.PriceCode = 'HDBK' or a.PriceCode = 'PDBK')


UNION ALL

SELECT        a.CustomerNo,a.CustomerName,c.ItemCode,c.ItemCodeDesc,b.ProductLine,b.PriceCode,b.CustomerPriceLevel,
            case
                when b.CustomerPriceLevel = 'J' and b.PriceCode = 'PKG' Then c.StandardUnitPrice*1.15
                when b.CustomerPriceLevel = 'D' and b.PriceCode = 'PKG' Then c.StandardUnitPrice*1.2
                when b.CustomerPriceLevel = 'V' and b.PriceCode = 'PKG' Then c.StandardUnitPrice*1.25
                when b.CustomerPriceLevel = 'C' and b.PriceCode = 'PKG' Then c.StandardUnitPrice*1.3
                when b.CustomerPriceLevel = 'RJ' and b.PriceCode = 'PKG' Then c.StandardUnitCost*1.15
                when b.CustomerPriceLevel = 'RD' and b.PriceCode = 'PKG' Then c.StandardUnitCost*1.2
                when b.CustomerPriceLevel = 'RV' and b.PriceCode = 'PKG' Then c.StandardUnitCost*1.25
                when b.CustomerPriceLevel = 'RC' and b.PriceCode = 'PKG' Then c.StandardUnitCost*1.3
                when b.CustomerPriceLevel = 'J' and b.PriceCode = 'COOL' Then c.StandardUnitCost*1.20
                when b.CustomerPriceLevel = 'D' and b.PriceCode = 'COOL' Then c.StandardUnitCost*1.25
                when b.CustomerPriceLevel = 'V' and b.PriceCode = 'COOL' Then c.StandardUnitCost*1.35
                when b.CustomerPriceLevel = 'C' and b.PriceCode = 'COOL' Then c.StandardUnitCost*1.45
                when b.CustomerPriceLevel = 'RV' and b.PriceCode = 'COOL' Then c.StandardUnitCost*1.1
                when b.CustomerPriceLevel = 'RC' and b.PriceCode = 'COOL' Then c.StandardUnitCost*1.15
                else 0
                end as [Price], 3 as [Priority]     
        
FROM            dbo.AR_Customer AS a
                inner join dbo.IM040_CustomerSpecialPricing as b on a.CustomerNo = b. CustomerNo
                inner join dbo.CI_Item as c on c.PriceCode = b.PriceCode and c.ProductLine=b.ProductLine            
where c.PrimaryVendorNo = '0000002' and b.ProductLine<>'' and  c.InactiveItem = 'N' and (b.PriceCode= 'PKG' or b.PriceCode='COOL')

UNION ALL

Select a.CustomerNo,c.CustomerName,b.ItemCode, b.ItemCodeDesc, a.ProductLine, a.PriceCode, a.CustomerPriceLevel,
        case
            when a.CustomerPriceLevel = 'J' and b.PriceCode = 'PKG' Then b.StandardUnitPrice*1.15
                when a.CustomerPriceLevel = 'D' and b.PriceCode = 'PKG' Then b.StandardUnitPrice*1.2
                when a.CustomerPriceLevel = 'V' and b.PriceCode = 'PKG' Then b.StandardUnitPrice*1.25
                when a.CustomerPriceLevel = 'C' and b.PriceCode = 'PKG' Then b.StandardUnitPrice*1.3
                when a.CustomerPriceLevel = 'RJ' and b.PriceCode = 'PKG' Then b.StandardUnitCost*1.15
                when a.CustomerPriceLevel = 'RD' and b.PriceCode = 'PKG' Then b.StandardUnitCost*1.2
                when a.CustomerPriceLevel = 'RV' and b.PriceCode = 'PKG' Then b.StandardUnitCost*1.25
                when a.CustomerPriceLevel = 'RC' and b.PriceCode = 'PKG' Then b.StandardUnitCost*1.3
                when a.CustomerPriceLevel = 'J' and b.PriceCode = 'COOL' Then b.StandardUnitCost*1.20
                when a.CustomerPriceLevel = 'D' and b.PriceCode = 'COOL' Then b.StandardUnitCost*1.25
                when a.CustomerPriceLevel = 'V' and b.PriceCode = 'COOL' Then b.StandardUnitCost*1.35
                when a.CustomerPriceLevel = 'C' and b.PriceCode = 'COOL' Then b.StandardUnitCost*1.45
                when a.CustomerPriceLevel = 'RV' and b.PriceCode = 'COOL' Then b.StandardUnitCost*1.1
                when a.CustomerPriceLevel = 'RC' and b.PriceCode = 'COOL' Then b.StandardUnitCost*1.15
                else 0
                end as [Price], 4 as [Priority]
From dbo.IM040_CustomerSpecialPricing as a
inner join dbo.CI_Item as b on a.PriceCode = b.PriceCode
inner join dbo.AR_Customer as c on a.CustomerNo = c.CustomerNo
where b.PrimaryVendorNo = '0000002' and b.InactiveItem = 'N' and a.ProductLine='' and (a.PriceCode = 'PKG' or a.PriceCode = 'COOL')

) as a

高效解决方案：使用窗口函数筛选最低优先级记录

方法1：ROW_NUMBER()窗口函数

通过ROW_NUMBER()给每个CustomerNo和ItemCode分组内的记录按Priority升序排序，取行号为1的记录（即最低优先级的那条）。这种方式性能优于WHERE NOT EXISTS，尤其是数据量较大时，数据库可以利用索引优化排序逻辑。

修改后的查询代码：

SELECT CustomerNo, CustomerName, ItemCode, ItemCodeDesc, ProductLine, PriceCode, CustomerPriceLevel, Price, Priority
FROM (
    SELECT 
        a.CustomerNo, a.CustomerName, a.ItemCode, a.ItemCodeDesc, a.ProductLine, a.PriceCode, a.CustomerPriceLevel,
        a.Price, a.Priority,
        -- 按CustomerNo和ItemCode分组，按Priority升序排序，生成行号
        ROW_NUMBER() OVER (PARTITION BY a.CustomerNo, a.ItemCode ORDER BY a.Priority ASC) AS RowNum
    FROM (
        -- 原UNION ALL子查询部分不变
        Select a.CustomerNo,b.CustomerName,c.ItemCode, c.ItemCodeDesc,c.ProductLine, c.PriceCode, 'Item Specific' as [CustomerPriceLevel], (c.StandardUnitCost + a.DiscountMarkup1) as [Price], 1 as [Priority]
        From dbo.IM_PriceCode as a
        inner join dbo.AR_Customer as b on a.CustomerNo=b.CustomerNo
        inner join dbo.CI_Item as c on a.ItemCode=c.ItemCode
        where c.PrimaryVendorNo='0000002' and c.InactiveItem='N' and (b.UDF_NATIONALACCNT='N' or b.UDF_NATIONALACCNT='') and a.PricingMethod <>'R'

        UNION ALL

        Select a.CustomerNo,c.CustomerName,b.ItemCode, b.ItemCodeDesc,a.ProductLine, a.PriceCode, a.CustomerPriceLevel, (b.StandardUnitCost + d.DiscountMarkup1) as [Price], 2 as [Priority]
        From dbo.IM040_CustomerSpecialPricing as a
        inner join dbo.CI_Item as b on a.PriceCode = b.PriceCode and b.ProductLine = a.ProductLine
        inner join dbo.IM_PriceCode as d on b.ItemCode = d.ItemCode and d.CustomerPriceLevel = a.CustomerPriceLevel
        inner join dbo.AR_Customer as c on a.CustomerNo = c.CustomerNo
        where b.PrimaryVendorNo = '0000002' and b.InactiveItem = 'N' and a.ProductLine <> '' and (a.PriceCode = 'BLK' or a.PriceCode = 'HDBK' or a.PriceCode = 'PDBK')

        UNION ALL

        SELECT        a.CustomerNo,a.CustomerName,c.ItemCode,c.ItemCodeDesc,b.ProductLine,b.PriceCode,b.CustomerPriceLevel,
                    case
                        when b.CustomerPriceLevel = 'J' and b.PriceCode = 'PKG' Then c.StandardUnitPrice*1.15
                        when b.CustomerPriceLevel = 'D' and b.PriceCode = 'PKG' Then c.StandardUnitPrice*1.2
                        when b.CustomerPriceLevel = 'V' and b.PriceCode = 'PKG' Then c.StandardUnitPrice*1.25
                        when b.CustomerPriceLevel = 'C' and b.PriceCode = 'PKG' Then c.StandardUnitPrice*1.3
                        when b.CustomerPriceLevel = 'RJ' and b.PriceCode = 'PKG' Then c.StandardUnitCost*1.15
                        when b.CustomerPriceLevel = 'RD' and b.PriceCode = 'PKG' Then c.StandardUnitCost*1.2
                        when b.CustomerPriceLevel = 'RV' and b.PriceCode = 'PKG' Then c.StandardUnitCost*1.25
                        when b.CustomerPriceLevel = 'RC' and b.PriceCode = 'PKG' Then c.StandardUnitCost*1.3
                        when b.CustomerPriceLevel = 'J' and b.PriceCode = 'COOL' Then c.StandardUnitCost*1.20
                        when b.CustomerPriceLevel = 'D' and b.PriceCode = 'COOL' Then c.StandardUnitCost*1.25
                        when b.CustomerPriceLevel = 'V' and b.PriceCode = 'COOL' Then c.StandardUnitCost*1.35
                        when b.CustomerPriceLevel = 'C' and b.PriceCode = 'COOL' Then c.StandardUnitCost*1.45
                        when b.CustomerPriceLevel = 'RV' and b.PriceCode = 'COOL' Then c.StandardUnitCost*1.1
                        when b.CustomerPriceLevel = 'RC' and b.PriceCode = 'COOL' Then c.StandardUnitCost*1.15
                        else 0
                        end as [Price], 3 as [Priority]     
                
        FROM            dbo.AR_Customer AS a
                        inner join dbo.IM040_CustomerSpecialPricing as b on a.CustomerNo = b. CustomerNo
                        inner join dbo.CI_Item as c on c.PriceCode = b.PriceCode and c.ProductLine=b.ProductLine            
        where c.PrimaryVendorNo = '0000002' and b.ProductLine<>'' and  c.InactiveItem = 'N' and (b.PriceCode= 'PKG' or b.PriceCode='COOL')

        UNION ALL

        Select a.CustomerNo,c.CustomerName,b.ItemCode, b.ItemCodeDesc, a.ProductLine, a.PriceCode, a.CustomerPriceLevel,
                case
                    when a.CustomerPriceLevel = 'J' and b.PriceCode = 'PKG' Then b.StandardUnitPrice*1.15
                        when a.CustomerPriceLevel = 'D' and b.PriceCode = 'PKG' Then b.StandardUnitPrice*1.2
                        when a.CustomerPriceLevel = 'V' and b.PriceCode = 'PKG' Then b.StandardUnitPrice*1.25
                        when a.CustomerPriceLevel = 'C' and b.PriceCode = 'PKG' Then b.StandardUnitPrice*1.3
                        when a.CustomerPriceLevel = 'RJ' and b.PriceCode = 'PKG' Then b.StandardUnitCost*1.15
                        when a.CustomerPriceLevel = 'RD' and b.PriceCode = 'PKG' Then b.StandardUnitCost*1.2
                        when a.CustomerPriceLevel = 'RV' and b.PriceCode = 'PKG' Then b.StandardUnitCost*1.25
                        when a.CustomerPriceLevel = 'RC' and b.PriceCode = 'PKG' Then b.StandardUnitCost*1.3
                        when a.CustomerPriceLevel = 'J' and b.PriceCode = 'COOL' Then b.StandardUnitCost*1.20
                        when a.CustomerPriceLevel = 'D' and b.PriceCode = 'COOL' Then b.StandardUnitCost*1.25
                        when a.CustomerPriceLevel = 'V' and b.PriceCode = 'COOL' Then b.StandardUnitCost*1.35
                        when a.CustomerPriceLevel = 'C' and b.PriceCode = 'COOL' Then b.StandardUnitCost*1.45
                        when a.CustomerPriceLevel = 'RV' and b.PriceCode = 'COOL' Then b.StandardUnitCost*1.1
                        when a.CustomerPriceLevel = 'RC' and b.PriceCode = 'COOL' Then b.StandardUnitCost*1.15
                        else 0
                        end as [Price], 4 as [Priority]
        From dbo.IM040_CustomerSpecialPricing as a
        inner join dbo.CI_Item as b on a.PriceCode = b.PriceCode
        inner join dbo.AR_Customer as c on a.CustomerNo = c.CustomerNo
        where b.PrimaryVendorNo = '0000002' and b.InactiveItem = 'N' and a.ProductLine='' and (a.PriceCode = 'PKG' or a.PriceCode = 'COOL')
    ) AS a
) AS ranked
WHERE ranked.RowNum = 1;

方法2：CTE结合窗口函数（可读性更强）

如果觉得嵌套子查询可读性差，可以用CTE（公共表表达式）重构代码，逻辑和方法1完全一致：

WITH AllPricingRecords AS (
    -- 原UNION ALL子查询部分不变
    Select a.CustomerNo,b.CustomerName,c.ItemCode, c.ItemCodeDesc,c.ProductLine, c.PriceCode, 'Item Specific' as [CustomerPriceLevel], (c.StandardUnitCost + a.DiscountMarkup1) as [Price], 1 as [Priority]
    From dbo.IM_PriceCode as a
    inner join dbo.AR_Customer as b on a.CustomerNo=b.CustomerNo
    inner join dbo.CI_Item as c on a.ItemCode=c.ItemCode
    where c.PrimaryVendorNo='0000002' and c.InactiveItem='N' and (b.UDF_NATIONALACCNT='N' or b.UDF_NATIONALACCNT='') and a.PricingMethod <>'R'

    UNION ALL

    Select a.CustomerNo,c.CustomerName,b.ItemCode, b.ItemCodeDesc,a.ProductLine, a.PriceCode, a.CustomerPriceLevel, (b

SQL查询优化：高效获取客户与商品的最低Priority定价

高效解决方案：使用窗口函数筛选最低优先级记录 #

方法1：ROW_NUMBER()窗口函数 #

方法2：CTE结合窗口函数（可读性更强） #

高效解决方案：使用窗口函数筛选最低优先级记录

方法1：ROW_NUMBER()窗口函数

方法2：CTE结合窗口函数（可读性更强）