数据库有这样的表结构商品编号 批号 数量
SP0001 001 50
SP0001 002 80
SP0001 003 40
SP0001 004 70
SP0001 005 50想写一个SQL语句（方法，存储过程都行） 传入商品编号 SP001 和数量 200 自动取得
数据库中 对应200个数量的 记录。 比如此时为：
SP0001 001 50
SP0001 002 80
SP0001 003 40
SP0001 004 30 （50+80+40+30=200）然后将这4条记录添加到画面上的表格里面。
求高手赐教！！！！
1 5
2 3
3 2
4 5
5 4
6 5
7 3
8 2
9 4
10 3
declare @t table(ID int,NUM int)
insert into @t select 1,5
union all select 2,3
union all select 3,2
union all select 4,5
union all select 5,4
union all select 6,5
union all select 7,3
union all select 8,2
union all select 9,4
union all select 10,3
select
top 10 rtrim(ID1)
+isnull(','+rtrim(ID2),'')
+isnull(','+rtrim(ID3),'')
+isnull(','+rtrim(ID4),'')
+isnull(','+rtrim(ID5),'')
+isnull(','+rtrim(ID6),'')
from
(select
a.id as id1,
b.id as id2,
c.id as id3,
d.id as id4,
e.id as id5,
f.id as id6
from
@t a,
(select * from @t union select null,null) b,
(select * from @t union select null,null) c,
(select * from @t union select null,null) d,
(select * from @t union select null,null) e,
(select * from @t union select null,null) f
where
a.id<isnull(b.id,995)
and
isnull(b.id,995)<isnull(c.id,996)
and
isnull(c.id,996)<isnull(d.id,997)
and
isnull(d.id,997)<isnull(e.id,998)
and
isnull(e.id,998)<isnull(f.id,999)
and
(a.NUM+isnull(b.NUM,0)+isnull(c.NUM,0)+isnull(d.NUM,0)+isnull(e.NUM,0)+isnull(f.NUM,0))=15
) t
order by
newid()--sql2005的一种解法：
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[NUM] int)
insert [tb]
select 1,5 union all
select 2,3 union all
select 3,2 union all
select 4,5 union all
select 5,4 union all
select 6,5 union all
select 7,3 union all
select 8,2 union all
select 9,4 union all
select 10,3
go
--select * from [tb]with szx as
(
select *,path=cast(id as varchar(8000)),total=num from tb
union all
select b.id,b.num,a.path+'-'+rtrim(b.id),a.total+b.num
from szx a join tb b on a.id<b.id and a.total<15
)
select id,num from tb,(select top 1 path from szx where total=15 order by newid()) a
where charindex('-'+rtrim(id)+'-','-'+path+'-')>0
--1.
/*
2 3
3 2
4 5
6 5
*/
--2.
/*
2 3
3 2
4 5
8 2
10 3
*/
--3....
--sql2005的一种解法：
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[NUM] int)
insert [tb]
select 1,5 union all
select 2,3 union all
select 3,2 union all
select 4,5 union all
select 5,4 union all
select 6,5 union all
select 7,3 union all
select 8,2 union all
select 9,4 union all
select 10,3
go
--select * from [tb]with szx as
(
select *,path=cast(id as varchar(8000)),total=num from tb
union all
select b.id,b.num,a.path+'-'+rtrim(b.id),a.total+b.num
from szx a join tb b on a.id<b.id and a.total<15
)
select id,num from tb,(select top 1 path from szx where total=15 order by path) a
where charindex('-'+rtrim(id)+'-','-'+path+'-')>0
order by ID
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
-- QQ :763157698
-- Development Tool :Microsoft Visual C++ 6.0 C Language
-- FUNCTION :CONVERT DATA TO T-SQL
---------------------------------------------------------------
-- Microsoft SQL Server 2005
-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]
---------------------------------------------------------------
---------------------------------------------------------------use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
商品编号 char(7),
批号 char(4),
数量 int
)
go
--插入测试数据
insert into tb select 'SP0001','001',50
union all select 'SP0001','002',80
union all select 'SP0001','003',40
union all select 'SP0001','004',70
union all select 'SP0001','005',50
go
--代码实现select *
from(
select 商品编号,
批号,
case when (select sum(数量) from tb where 批号<=t.批号)<=200 then 数量
else 数量+200-(select sum(数量) from tb where 批号<=t.批号) end 数量
from tb t
)temp
where 数量>0/*结果
商品编号 批号 数量
--------------------------
SP0001 001 50
SP0001 002 80
SP0001 003 40
SP0001 004 30
*/
b.*
from
(select 商品编号,批号,数量=(select sum(数量) from tb where 商品编号=t.商品编号 and 批号<=t.批号) from tb t )a,
tb b
where
a.商品编号=b.商品编号 and a.批号=b.批号
and
a.数量<200