问题描述：
供应商报价,有些会在采购量大时给一些优惠. 数据库中物料报价表:
t(PartCode,VendCode,QtyFrom, QtyTo, Price)
(物料编码, 厂商代码, 采购数量从, 采购数量至, 单价)
现在要从上表查询得到一个临时表,表结构与上表相同。要求同一物料,同一数量时的最低单价及相应供应商.例如表中有下列数据:
pA,vA,100,10
pA,101,-1,8 --(-1代表无穷大)
pA,vB,9
pA,vC,1000,7.5
在临时表中应该是这样的：
pA,999,8
pA,7.5
各供应商供应区间可能有重叠，且不完全，即从0到-1所有区间。
sql语句：
declare @t table (id int identity ( 1 , 1 ),PartCode varchar ( 20 ),QtyFrom int ,qOrd int )
insert into @t
select distinct PartCode, case QtyFrom when - 1 then 1 else 0 end as qord from t union
select distinct PartCode, case QtyTo when - 1 then - 1 else QtyTo + 1 end , case QtyTo when - 1 then 1 else 0 end from t a
where not exists ( select * from t where PartCode = a.PartCode and QtyFrom = a.QtyTo + 1 and QtyFrom <> 0 )
order by PartCode, case QtyFrom when - 1 then 1 else 0 end ,QtyFrom
-- select * from @t
select a.PartCode,a.VendCode,v1.QtyFrom as QtyFrom, case v2.QtyFrom when - 1 then - 1 else v2.QtyFrom - 1 end as QtyTo,a.Price
from @t v1 join @t v2 on v1.id = v2.id - 1 and v1.PartCode = V2.PartCode
join t a on v1.Partcode = a.PartCode
where a.QtyFrom <= v1.QtyFrom and case a.QtyTo when - 1 then - 2 else v1.QtyFrom end <= a.QtyTo
and not exists
( select * from t where PartCode = a.PartCode and Price < a.Price and QtyFrom <= v1.QtyFrom and
case qtyto when - 1 then - 2 else v1.QtyFrom end <= QtyTo)