SQL Select 最小优先级

我有一个查询，它有针对每个客户的不同定价场景的四个联合子查询.在每个联合中，我都分配了一个值作为优先级，从最低到最高对场景进行排名.但是，当我运行查询时，如果客户在场景1中有产品价格，它还会显示场景2、3和4的价格.我try Select MIN(a.Priority)，但仍然显示每个联盟的价格.我用WHERE not exits (SELECT 1 FROM ("Previous Union")就能做到这一点，但有没有更有效的方法呢？以下是我的疑问.

澄清我的问题.我正在try 返回每个客户和项目代码的最低优先级.

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