分段计价情况下最低价格供应商选择问题

问题描述：

供应商报价,有些会在采购量大时给一些优惠. 数据库中物料报价表:

t(PartCode,  VendCode,  QtyFrom,  　　QtyTo,  　　Price)
(物料编码,　厂商代码,　采购数量从,　采购数量至,　单价)

现在要从上表查询得到一个临时表,表结构与上表相同。要求同一物料,同一数量时的最低单价及相应供应商.例如表中有下列数据:

pA,vA,0,100,10
pA,vA,101,-1,8  --(-1代表无穷大)
pA,vB,0,-1,9
pA,vC,1000,-1,7.5

在临时表中应该是这样的：
pA,vB,0,100,9
pA,vA,101,999,8
pA,vC,1000,-1,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,QtyFrom,
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)