供应商报价,有些会在采购量大时给一些优惠. 数据库中物料报价表: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
(物料编码, 厂商代码, 采购数量从, 采购数量至, 单价)现在要从上表查询得到一个临时表,表结构与上表相同。要求同一物料,同一数量时的最低单价及相应供应商.例如表中有下列数据: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
解决方案 »
- 关于日期加减操作后绑定到gridview的问题
- 想投身Msqlserver门下,大家给点意见
- sql2005中如何用bcp? bcp vrdn.. recinsurance out c:\testlog.txt -c -t"|" -U startmaintuser -P start14 -S tfistrdv001 -b 50
- sql2008附加数据库时出现以上错误
- 如何把服务器上的所有资源全部备份到另一台机上
- 再求加工最近单价求函数和视图
- 在线求助一个关于sql的日期问题,急
- 这个存储过程为嘛得不到任何东东?
- 请帮忙描述一下ADO.NET和ADO之间的区别.
- 真的没有人能够帮忙吗?解决后送100分!在线等待!
- SQL2005 导出EXCEL
- 怎么把SQL SERVER里 A数据库的a表添加到B数据库里?
select m.* from tb m where not exists (select 1 from tb n where PartCode = m.PartCode and Price < m.Price)
指定一个产品和数量,查询其最低单价是不难. 现在是因为这个查询是在一个更加复杂的算法中的一部分,我用游标处理每一笔数据, 那如果直接用原表,每次查询都要先聚合,再比较(当然这两步是在一个SQl语句中),这样效率会比较低.所以希望先求出最低单价,再进行游标操作, 这样游标操作中的效率会更高.
drop table t
go
--(物料编码, 厂商代码, 采购数量从, 采购数量至, 单价)
create table t(PartCode varchar(20), VendCode varchar(20), QtyFrom int,QtyTo int,Price numeric(12,2)) goinsert into t
select 'pA','vA',0,100,10
union all select 'pA','vA',101,-1,8 --(-1代表无穷大)
union all select 'pA','vB',0,-1,9
union all select 'pA','vC',1000,-1,7.5
declare @num int
select @num=143select PartCode,VendCode,price
from t a
where QtyFrom<=@num and case qtyto when -1 then -2 else qtyto end <=qtyto
and not exists
(select * from t where PartCode=a.PartCode and Price<a.Price and QtyFrom<=@num and
case qtyto when -1 then -2 else qtyto end <=qtyto)
insert into @t
select distinct partcode,QtyFrom
from t
order by PartCode,QtyFrom
select t1.PartCode,t1.QtyFrom,isnull(t2.QtyFrom,0)-1 as QtyTo
from @t t1 left join @t t2 on t1.PartCode=t2.PartCode and t1.id=t2.id-1
为什么要用游标呢?
不知道你的具体情况,看下面的代码:if(OBJECT_ID('t') is not null)
drop table t
go
--(物料编码, 厂商代码, 采购数量从, 采购数量至, 单价)
create table t(PartCode varchar(20), VendCode varchar(20), QtyFrom int,QtyTo int,Price numeric(12,2)) goinsert into t
select 'pA','vA',0,100,10
union all select 'pA','vA',101,-1,8 --(-1代表无穷大)
union all select 'pA','vB',0,-1,9
union all select 'pA','vC',1000,-1,7.5 go
if(OBJECT_ID('tt') is not null )
drop table tt
go
create table tt(partCode varchar(20),Qty int)
insert into tt
select 'pa',20
union all select 'pa',99
union all select 'pa',199
union all select 'pa',499
union all select 'pa',1999
select a.PartCode,VendCode,b.Qty,a.Price
from t a join tt b on a.PartCode=b.partCode
where QtyFrom<=b.Qty and case qtyto when -1 then -2 else qtyto end <=qtyto
and not exists
(select * from t where PartCode=a.PartCode and Price<a.Price and QtyFrom<=b.Qty and
case qtyto when -1 then -2 else qtyto end <=qtyto)建议楼主 加些分了,真费劲,呵呵。
PartCode VendCode Qty Price
===============================================
pA vA 199 8.00
pA vA 499 8.00
pA vB 20 9.00
pA vB 99 9.00
pA vC 1999 7.50update也差不多了,呵呵。
如果不是,LZ再开帖,我实现你的要求。
如果每个供应商的数量区间是连续的,同没有这个问题。如果有不连接的情况,建议将数据表先更新为连接的,或在查询时把qtyTo也加进来,然后将qtyto=qtyfrom+1的去掉就行了。在这就不写语句了。稍等,以上面 那个区间为基础,查询各区间的最低价格。
if(OBJECT_ID('t') is not null)
drop table t
go
--(物料编码, 厂商代码, 采购数量从, 采购数量至, 单价)
create table t(PartCode varchar(20), VendCode varchar(20), QtyFrom int,QtyTo int,Price numeric(12,2)) goinsert into t
select 'pA','vA',0,100,10
union all select 'pA','vA',101,600,8 --(-1代表无穷大)
union all select 'pA','vA',601,-1,7.7
union all select 'pA','vB',0,-1,8.3
union all select 'pA','vC',0,80,8.5
union all select 'pA','vC',81,200,8.2
union all select 'pA','vC',201,1000,7.9
union all select 'pA','vC',1001,-1,7.5 goif(OBJECT_ID('V_PQty') is not null)
drop view V_PQty
go
create view V_PQty as
select ROW_NUMBER() over(order by PartCode,QtyFrom) id,
PartCode,Qtyfrom
from (select distinct PartCode,Qtyfrom from t) tgo
select a.PartCode,a.VendCode,v1.QtyFrom as QtyFrom,isnull(v2.QtyFrom,0)-1 as QtyTo, a.Price
from V_PQty v1 left join V_PQty 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)
===============================================================================================
PartCode VendCode QtyFrom QtyTo Price
pA vB 0 80 8.30
pA vC 81 100 8.20
pA vA 101 200 8.00
pA vC 201 600 7.90
pA vA 601 1000 7.70
pA vC 1001 -1 7.50
看看能否满足你的要求
create table t(PartCode varchar(20), VendCode varchar(20), QtyFrom int,QtyTo int,Price numeric(12,2)) goinsert into t
select 'pA','vA',0,100,10
union all select 'pA','vA',101,600,8 --(-1代表无穷大)
union all select 'pA','vA',601,-1,7.7
union all select 'pA','vB',0,400,8.3
union all select 'pA','vC',0,80,8.5
union all select 'pA','vC',81,200,8.2
union all select 'pA','vC',201,1000,7.9
union all select 'pA','vC',1001,-1,7.5 declare @t table(id int identity(1,1),PartCode varchar(20),QtyFrom int)
insert into @t
select distinct PartCode,Qtyfrom from t union
select distinct PartCode,Qtyto from t a
where not exists(select * from t where PartCode=a.PartCode and QtyFrom=a.QtyTo+1)
order by PartCode,Qtyfrom select a.PartCode,a.VendCode,v1.QtyFrom as QtyFrom,isnull(v2.QtyFrom,0)-1 as QtyTo, a.Price
from @t v1 left 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) ================================================================================================
PartCode VendCode QtyFrom QtyTo Price
pA vB 0 80 8.30
pA vC 81 100 8.20
pA vA 101 200 8.00
pA vC 201 399 7.90
pA vC 400 600 7.90
pA vA 601 1000 7.70
pA vC 1001 -1 7.50
select 'pA','vA',0,100,10
union all select 'pA','vA',101,600,8 --(-1代表无穷大)
union all select 'pA','vA',601,-1,7.7
union all select 'pA','vB',0,400,8.3
union all select 'pA','vC',0,80,8.5
union all select 'pA','vC',81,200,8.2
union all select 'pA','vC',201,1000,7.9
union all select 'pA','vC',1001,-1,7.5
union all select 'pA','vd',500,900,7.7
union all select 'pA','vd',1200,1500,8.3
union all select 'pA','vd',3000,-1,7.4
union all select 'pA','ve',440,700,8.4
union all select 'pA','ve',400,3000,8.1
union all select 'pA','vf',0,40,7.3
declare @t table(id int identity(1,1),PartCode varchar(20),QtyFrom int)
insert into @t
select distinct PartCode,Qtyfrom from t union
select distinct PartCode,Qtyto from t a
where not exists(select * from t where PartCode=a.PartCode and QtyFrom=a.QtyTo+1)
order by PartCode,Qtyfrom select * from @t
1 pA 0
2 pA 40
3 pA 81
4 pA 101
5 pA 201
6 pA 400
7 pA 440
8 pA 500
9 pA 601
10 pA 700
11 pA 900
12 pA 1001
13 pA 1200
14 pA 1500
15 pA 3000LZ有数据吗?可以发到我邮箱。我这试不出问题了。
[email protected]
drop table t
gocreate table t(PartCode varchar(20), VendCode varchar(20), QtyFrom int,QtyTo int,Price numeric(12,2)) goinsert into t
select 'pA','vA',0,100,10
union all select 'pA','vA',101,600,8 --(-1代表无穷大)
union all select 'pA','vA',601,-1,7.7
union all select 'pA','vB',0,400,8.3
union all select 'pA','vC',0,80,8.5
union all select 'pA','vC',81,200,8.2
union all select 'pA','vC',201,1000,7.9
union all select 'pA','vC',1001,-1,7.5
union all select 'pA','vd',500,900,7.7
union all select 'pA','vd',1200,1500,8.3
union all select 'pA','vd',3000,-1,7.4
union all select 'pA','ve',440,700,8.4
union all select 'pA','ve',400,3000,8.1
union all select 'pA','vf',0,40,7.3 go
declare @t table(id int identity(1,1),PartCode varchar(20),QtyFrom int)
insert into @t
select distinct PartCode,Qtyfrom from t union
select distinct PartCode,Qtyto from t a
where not exists(select * from t where PartCode=a.PartCode and QtyFrom=a.QtyTo+1)
order by PartCode,Qtyfromselect a.PartCode,a.VendCode,v1.QtyFrom as QtyFrom,isnull(v2.QtyFrom,0)-1 as QtyTo, a.Price
from @t v1 left 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) =========================================================================================
查询结果:
PartCode VendCode QtyFrom QtyTo Price
pA vf 0 39 7.30
pA vf 40 80 7.30
pA vC 81 100 8.20
pA vA 101 200 8.00
pA vC 201 399 7.90
pA vC 400 439 7.90
pA vC 440 499 7.90
pA vd 500 600 7.70
pA vA 601 699 7.70
pA vd 601 699 7.70
pA vA 700 899 7.70
pA vd 700 899 7.70
pA vA 900 1000 7.70
pA vd 900 1000 7.70
pA vC 1001 1199 7.50
pA vC 1200 1499 7.50
pA vC 1500 2999 7.50
pA vd 3000 -1 7.40*******************************************************************
查询结果中,相同数量区间有多条相同记录,是因为不同供应商在该区间的供应价格是相等的,且都是最低。
个人感觉这个结果是可以显示的,此时就可以根据需要先把不同的供应商。
在原始数据中并没有这个报价啊.我另外试着加了一行原始数据,物料pB:
union all select 'pB','vA',0,2000,5
结果中也给多出来了2000到无穷大的区间的报价.结果显示是这样:
pB vA 0 1999 5
pB vA 2000 -1 5
昨晚10点多你还在思考解答这个问题, 非常感谢.
select 'pB','vA',20,2000,5
union all select 'pB','vB',2000,5000, 6正确的结果应该显示如下:
pB vA 20 2000 5 --分界点划到最低单价上.
pB vB 2001 5000 6上面的查询算法,显示结果为:
pB vA 20 1999 5.00
pB vA 2000 4999 5.00 --此部分应该是单价6为准.
pB vB 5000 -1 6.00 --此部分没有报价.
insert into @t
select distinct PartCode,Qtyfrom from t union
select distinct PartCode,Qtyto+1 from t a
where not exists(select * from t where PartCode=a.PartCode and QtyFrom=a.QtyTo+1)
order by PartCode,Qtyfromselect a.PartCode,a.VendCode,v1.QtyFrom as QtyFrom,
isnull(v2.QtyFrom,0)-1case when v2.QtyFrom is null and exists(select * from t wher qty_to=-1 ) then -1 when
v2.QtyFrom is not null then v2.QtyFrom end as QtyTo, a.Price
from @t v1 left 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) 不过,上面的语句也存在问题的,如果没有-1那么应该不显示的。
但还是显示为null一行的。
isnull的判断也不要了。
drop table t
gocreate table t(PartCode varchar(20), VendCode varchar(20), QtyFrom int,QtyTo int,Price numeric(12,2)) goinsert into t
select 'pA','vA',0,100,10
union all select 'pA','vA',101,600,8 --(-1代表无穷大)
union all select 'pA','vA',601,-1,7.7
union all select 'pA','vB',0,400,8.3
union all select 'pA','vC',0,80,8.5
union all select 'pA','vC',81,200,8.2
union all select 'pA','vC',201,1000,7.9
union all select 'pA','vC',1001,-1,7.5
union all select 'pA','vd',500,900,7.7
union all select 'pA','vd',1201,1500,8.3
union all select 'pA','vd',3001,-1,7.4
union all select 'pA','ve',441,700,8.4
union all select 'pA','ve',401,3000,8.1
union all select 'pA','vf',0,40,7.3 go
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 @tselect 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
PartCode VendCode QtyFrom QtyTo Price
pA vf 0 40 7.30
pA vB 41 80 8.30
pA vC 81 100 8.20
pA vA 101 200 8.00
pA vC 201 400 7.90
pA vC 401 439 7.90
pA vC 440 499 7.90
pA vd 500 600 7.70
pA vA 601 700 7.70
pA vd 601 700 7.70
pA vA 701 900 7.70
pA vd 701 900 7.70
pA vA 901 1000 7.70
pA vC 1001 1200 7.50
pA vC 1201 1500 7.50
pA vC 1501 3000 7.50
pA vd 3001 -1 7.40
========================================================
再做个没有-1的。truncate table tinsert into t
select 'pA','vA',0,100,10
union all select 'pA','vA',101,600,8 --(-1代表无穷大)
union all select 'pA','vB',0,400,8.3
union all select 'pA','vC',0,80,8.5
union all select 'pA','vC',81,200,8.2
union all select 'pA','vC',201,1000,7.9
union all select 'pA','vd',500,900,7.7
union all select 'pA','vd',1201,1500,8.3
union all select 'pA','ve',441,700,8.4
union all select 'pA','ve',401,3000,8.1
union all select 'pA','vf',0,40,7.3 go
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 @tselect 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)
PartCode VendCode QtyFrom QtyTo Price
pA vf 0 40 7.30
pA vB 41 80 8.30
pA vC 81 100 8.20
pA vA 101 200 8.00
pA vC 201 400 7.90
pA vC 401 440 7.90
pA vC 441 499 7.90
pA vd 500 600 7.70
pA vd 601 700 7.70
pA vd 701 900 7.70
pA vC 901 1000 7.90
pA ve 1001 1200 8.10
pA ve 1201 1500 8.10
pA ve 1501 3000 8.10
declare @tb table ([PartCode] varchar(2),[VendCode] varchar(2),[QtyFrom] int,[QtyTo] int,[Price] numeric(4,2))
insert into @tbselect 'ph','vA',10,100,2 union all
select 'ph','v1',200,301,6 union allselect 'pA','vA',0,100,10 union all
select 'pA','yy',100,-1,8 union all
select 'pA','vd',10,30,18 union all
select 'pA','vB',0,-1,9 union all
select 'pA','vB',500,700,6 union all
select 'pA','vC',1000,-1,6.5;with s as
(
select *
from(
select * ,1 level,row_number()over(partition by [PartCode] order by price) idx
from @tb
where ([QtyFrom]<= [QtyTo] or [QtyTo]=-1)
) d
where idx = 1
union all
select t.partcode,t.vendcode
,(case when t.qtyFrom>s.qtyto then t.qtyFrom else s.qtyto+1 end)
,t.qtyto
,t.price
,s.level+1,row_number()over(partition by t.partcode order by t.price) from s inner join @tb t
on t.[PartCode] = s.[PartCode]
and (t.[QtyFrom]<= t.[QtyTo] or t.[QtyTo]=-1)
and (s.qtyto<>-1 and(t.qtyto=-1 or t.qtyto>s.qtyto))
and s.idx = 1
and s.level < 100
)
,s2 as
(
select *
from(
select * ,1 level,row_number()over(partition by [PartCode] order by price) idx
from @tb
where ([QtyFrom]<= [QtyTo] or [QtyTo]=-1)
) d
where idx = 1
union all
select t.partcode,t.vendcode
,t.qtyFrom
,(case when ((t.qtyto<>-1)and(t.qtyto<s2.qtyfrom)) then t.qtyto else s2.qtyfrom-1 end)
,t.price
,s2.level+1,row_number()over(partition by t.partcode order by t.price) from s2 inner join @tb t
on t.[PartCode] = s2.[PartCode]
and (t.[QtyFrom]<= t.[QtyTo] or t.[QtyTo]=-1)
and t.[QtyFrom]<s2.[QtyFrom]
and s2.idx = 1
)select
partcode,[VendCode],[QtyFrom],[QtyTo],[Price]
from(
select * from s where idx = 1
union
select * from s2 where idx = 1
) t
order by partcode,qtyfrom
partcode VendCode QtyFrom QtyTo Price
-------- -------- ----------- ----------- ---------------------------------------
pA vB 0 99 9.00
pA yy 100 499 8.00
pA vB 500 700 6.00
pA vC 1000 -1 6.50
ph vA 10 100 2.00
ph v1 200 301 6.00(6 行)
declare @tb table ([PartCode] varchar(6),[VendCode] varchar(5),[QtyFrom] int,[QtyTo] int,[Price] numeric(4,2))
insert into @tbselect 'ph','vA',10,100,2 union all
select 'ph','v1',200,301,6 union allselect 'pA','vA23',0,50,10 union all
select 'pA','vA12',0,50,10 union all
select 'pA','yy',110,-1,11 union all
select 'pA','vd',10,30,3 union all
select 'pA','vB',0,-1,12 union all
select 'pA','vB',500,700,16 union all
select 'pA','vC',0,-1,16.5;with s as
(
select *
from(
select * ,1 level,row_number()over(partition by [PartCode] order by price) idx
from @tb
where ([QtyFrom]<= [QtyTo] or [QtyTo]=-1)
) d
where idx = 1
union all
select t.partcode,t.vendcode
,(case when t.qtyFrom>s.qtyto then t.qtyFrom else s.qtyto+1 end)
,t.qtyto
,t.price
,s.level+1
,row_number()over(partition by t.partcode order by t.price)
from s inner join @tb t
on t.[PartCode] = s.[PartCode]
and (t.[QtyFrom]<= t.[QtyTo] or t.[QtyTo]=-1)
and (s.qtyto<>-1 and(t.qtyto=-1 or t.qtyto>s.qtyto))
and s.idx = 1
and s.level < 100
)
,s2 as
(
select *
from(
select * ,1 level,row_number()over(partition by [PartCode] order by price) idx
from @tb
where ([QtyFrom]<= [QtyTo] or [QtyTo]=-1)
) d
where idx = 1
union all
select t.partcode,t.vendcode
,t.qtyFrom
,(case when ((t.qtyto<>-1)and(t.qtyto<s2.qtyfrom)) then t.qtyto else s2.qtyfrom-1 end)
,t.price
,s2.level+1
,row_number()over(partition by t.partcode order by t.price)
from s2 inner join @tb t
on t.[PartCode] = s2.[PartCode]
and (t.[QtyFrom]<= t.[QtyTo] or t.[QtyTo]=-1)
and t.[QtyFrom]<s2.[QtyFrom]
and s2.idx = 1
)
,s3 as
(
select
partcode,[VendCode],[QtyFrom],[QtyTo],[Price]
,row_number()over(partition by partcode order by qtyfrom)idx
from(
select * from s where idx = 1
union
select * from s2 where idx = 1
) t
)
,s4 as
(
select
t.partcode,t.[VendCode],(t1.[QtyTo]+1) [QtyFrom],(t2.[QtyFrom]-1)[QtyTo],t.[Price]
,row_number()over(partition by t.partcode,t.qtyfrom order by t.price)idx
from @tb t
inner join s3 t1
on t.partcode = t1.partcode
inner join s3 t2
on t.partcode = t2.partcode
and t1.qtyto + 1 < t2.qtyfrom
and t1.idx+1 = t2.idx
where
t1.qtyto>=t.qtyfrom
and (t2.qtyfrom <= t.qtyto or t.qtyto = -1)
)
,s5 as
(
select partcode,[VendCode],[QtyFrom],[QtyTo],[Price] from s3
union
select partcode,[VendCode],[QtyFrom],[QtyTo],[Price] from s4 where idx = 1
)
select * from s5 order by partcode,[QtyFrom]
partcode VendCode QtyFrom QtyTo Price
-------- -------- ----------- ----------- ---------------------------------------
pA vA23 0 9 10.00
pA vd 10 30 3.00
pA vA23 31 50 10.00
pA vB 51 109 12.00
pA yy 110 -1 11.00
ph vA 10 100 2.00
ph v1 200 301 6.00(7 行)
我的数据库是SQL Server 2000, SQL Server 2005的语法不适合.