|
问题描述：
供应商报价,有些会在采购量大时给一些优惠. 数据库中物料报价表:
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)
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
