用完旧料用新旧：@a是需求表，@b是库存表,要得到@c。

蒋老师出的题：不用游标，能实现吗？
(测试良久，未果)

--计划:ID为产品ID，V是用料量，P1原料，P2，P3，P4分别为第一，第二第三代用料，
--当主原料用完，可用代用料，按顺序P1用完用P2，然后用P3。
declare @a table(id int,v int,p1 int,p2 int,p3 int,p4 int)
insert @a select 1,500,1,3,5,7
insert @a select 2,500,2,1,5,7
insert @a select 3,500,1,3,5,7
insert @a select 4,500,2,1,5,7
--原料存量
declare @b table(id int,C int)
insert @b select 1,600
insert @b select 2,300
insert @b select 3,300
insert @b select 4,100
insert @b select 5,600
insert @b select 6,200
insert @b select 7,900
insert @b select 8,100
/*分配结果：
declare @C table(id int,v int,p1 int,p2 int,p3 int,p4 int)
insert @C select 1,500,500, 0, 0, 0
insert @C select 2,500,300,100,100, 0
insert @C select 3,500,0,300,200, 0
insert @C select 4,500,0, 0,300,200
*/
---------------------------------------------------------------

--半天只写出一个模拟游标的版本。本来想写个自定义函数就可以不用把表转化过来了，但想想还是不要函数会根更广泛一点。
--先粗粗写出来，虽然慢点，有空了再改进。
--另：根据这个思路似乎可以在@d出来以后，一句话写出来，还没想通就是了。
--计划:ID为产品ID，V是用料量，P1原料，P2，P3，P4分别为第一，第二第三代用料，
--当主原料用完，可用代用料，按顺序P1用完用P2，然后用P3。
declare @a table(id int,v int,p1 int,p2 int,p3 int,p4 int)
insert @a select 1,500,1,3,5,7
insert @a select 2,500,2,1,5,7
insert @a select 3,500,1,3,5,7
insert @a select 4,500,2,1,5,7
--原料存量
declare @b table(id int,C int)
insert @b select 1,600
insert @b select 2,300
insert @b select 3,300
insert @b select 4,100
insert @b select 5,600
insert @b select 6,200
insert @b select 7,900
insert @b select 8,100
/*分配结果：
declare @C table(id int,v int,p1 int,p2 int,p3 int,p4 int)
insert @C select 1,500,500, 0, 0, 0
insert @C select 2,500,300,100,100, 0
insert @C select 3,500,0,300,200, 0
insert @C select 4,500,0, 0,300,200
*/
declare @d table (cnt int identity(1,1),id int, Px int,v int,c_v int primary key (cnt))
insert into @d (id,px,v)

select id,p1,v from @a
union all
select id,p2,v from @a
union all
select id,p3,v from @a
union all
select id,p4,v from @a

--select * from @d order by id,cnt

declare
@cnt int,
@t1 int,
@t2 int,
@t3 int,
@tmp_cnt int

set @cnt=1
while @cnt <=(select max(cnt) from @d)
begin
set @tmp_cnt=(select cnt from @d d where (select count(*) from @d d2
where d2.id

  1<d.id 'xx'="" (d2.id="d.id" (select="" )="" --剩下多少="" @b="" @d="" @t1="" @t2="case" @t3="" @tmp_cnt="" @tmp_cnt)="" \--print="" \--总要="" \--本产品="" and="" c="" cnt="@tmp_cnt)" cnt<="" d2.cnt<="d.cnt)" from="" id="" isnull(sum(c_v),0)="" min(v)="" or="" px="" set="" when="" where="" 原料="" 已经用料量="" 用料="">@t2 and @t1&gt;0 then (case when @t1&gt;=(@t3-@t2) then (@t3-@t2) else @t1 end) else 0 end   
  2\--print @t2   
  3update d   
  4set   
  5c_v= @t2   
  6from @d d   
  7where cnt=@tmp_cnt   
  8set @cnt=@cnt+1   
  9end   
 10select * from @d order by id,cnt   
 11\---------------------------------------------------------------   
 12  
 13不使用cursor 减少循环的 IO?   
 14我真不知道你是怎么得出这个结论的.   
 15(还不算你把循环次数比游标增加4倍).   
 16你测一下下面的速度,那个快.   
 17  
 18select top 50000 identity(int,1,1) as id into #t from master..sysobjects A,master..sysobjects B   
 19用WHILE:   
 20declare @i int,@j int   
 21  
 22set @i=1   
 23while @i&lt;=50000   
 24begin   
 25select @j=id from #t where id=@i   
 26set @i=@i+1   
 27print @j   
 28end   
 29  
 30用游标:   
 31declare @i int   
 32  
 33declare a cursor for   
 34select id from #t order by id   
 35open a   
 36fetch next from a into @i   
 37while @@fetch_status=0   
 38begin   
 39print @i   
 40fetch next from a into @i   
 41end   
 42  
 43close a   
 44deallocate a   
 45\---------------------------------------------------------------   
 46  
 47用一个游标，   
 48set nocount on   
 49declare @a table(id int,v int,p1 int,p2 int,p3 int,p4 int, flag int)   
 50insert @a select 1,700,1,3,5,7,0   
 51insert @a select 2,500,2,1,5,7,0   
 52insert @a select 3,500,1,3,5,7,0   
 53insert @a select 4,500,2,1,5,7,0   
 54\--原料存量   
 55declare @b table(id int,C int)   
 56insert @b select 1,600   
 57insert @b select 2,300   
 58insert @b select 3,300   
 59insert @b select 4,100   
 60insert @b select 5,600   
 61insert @b select 6,200   
 62insert @b select 7,900   
 63insert @b select 8,100   
 64  
 65  
 66declare @p1 int,@v int,@d int,@p4 int ,@p int,@id int,@c int   
 67declare @t table(id int,v int,p1 int,p2 int,p3 int,p4 int)   
 68  
 69  
 70declare @ table(id int,v int,p int ,d int,c int,flag int)   
 71declare cur_a cursor for   
 72select distinct id from @a order by id   
 73open cur_a   
 74fetch next from cur_a into @id   
 75while @@fetch_status=0   
 76begin   
 77delete @   
 78insert into @ select a.id,v,p ,c,0,flag from(   
 79select id,v,p1 as p,1 flag from @a where id=@id union select id,0,p2 as p,2 flag from @a where id=@id union   
 80select id,0,p3 as p ,3 falg from @a where id=@id union select id,0,p4 as p ,4 flag from @a where id=@id) a   
 81inner join @b b on p=b.id order by a.id,flag   
 82  
 83if exists(select sum(v) from @ a having sum(v)&gt;sum(d))   
 84break   
 85  
 86update @ set @v = case when v=0 then @v - @c else v end , @c= case when d&gt;=@v then @v else d end,   
 87d=d-@c ,c=@c   
 88  
 89update @a set flag=1   
 90insert @t select @id,sum(v),sum(case when flag=1 then c else 0 end),sum(case when flag=2 then c else 0 end),   
 91sum(case when flag=3 then c else 0 end),sum(case when flag=4 then c else 0 end) from @   
 92update b set c=d   
 93from @ a inner join @b b on p=b.id   
 94fetch next from cur_a into @id   
 95  
 96end   
 97close cur_a   
 98deallocate cur_a   
 99set nocount off   
100select * from @t   
101  
102id v p1 p2 p3 p4   
103\----------- ----------- ----------- ----------- ----------- -----------   
1041 700 600 100 0 0   
1052 500 300 0 200 0   
1063 500 0 200 300 0   
1074 500 0 0 100 400   
108  
109\---------------------------------------------------------------   
110  
111/*   
112这类问题，在电子行业ERP替代料用法，成套产品用法上有经典的解决办法。   
113提的这个个案，怎么解决都是有缺憾的，为达到需求，简化查询，唯一途径是完善设计。   
114下述代码，进行了简单实现，但不能实现P1..Pn的扩展，   
115而且，假定了前提，我在表中定义了主键和约束   
116另外，表@a中，不同行中有相同的替代，所以只能一行一行地处理，不用cursor,我用了   
117ID递进的方法。   
118*/   
119  
120  
121DECLARE @CID INTEGER,   
122@BID INTEGER,   
123@QTY INTEGER   
124  
125DECLARE @A TABLE(ID INT PRIMARY KEY,V INT,P1 INT,P2 INT,P3 INT,P4 INT,UNIQUE(ID,P1,P2,P3,P4))   
126INSERT INTO @A   
127SELECT 1,500,1,3,5,7   
128UNION ALL SELECT 2,500,2,1,5,7   
129UNION ALL SELECT 3,500,1,3,5,7   
130UNION ALL SELECT 4,500,2,1,5,7   
131  
132DECLARE @B TABLE(ID INT PRIMARY KEY,C INT)   
133INSERT INTO @B   
134SELECT 1,600   
135UNION ALL SELECT 2,300   
136UNION ALL SELECT 3,300   
137UNION ALL SELECT 4,100   
138UNION ALL SELECT 5,600   
139UNION ALL SELECT 6,200   
140UNION ALL SELECT 7,900   
141UNION ALL SELECT 8,100   
142  
143DECLARE @CC TABLE(ID INT PRIMARY KEY,V INT,P1 INT,P2 INT,P3 INT,P4 INT)   
144INSERT INTO @CC   
145SELECT 1,500,500, 0, 0, 0   
146UNION ALL SELECT 2,500,300,100,100, 0   
147UNION ALL SELECT 3,500,0,300,200, 0   
148UNION ALL SELECT 4,500,0, 0,300,200   
149  
150\--&gt;按A.ID 依次处理   
151DECLARE @C TABLE(ID INT PRIMARY KEY,V INT,P1 INT,P2 INT,P3 INT,P4 INT)   
152INSERT INTO @C(ID,V,P1,P2,P3,P4) SELECT ID,V,0,0,0,0 FROM @A WHERE 1=1   
153  
154SELECT @CID = ISNULL(MIN(ID),-1) FROM @C WHERE V &gt; (P1+P2+P3+P4)   
155WHILE EXISTS(SELECT 1 FROM @C WHERE V &gt; (P1+P2+P3+P4) AND ID &gt;= @CID)   
156AND EXISTS(SELECT 1 FROM @B AS B WHERE B.C &gt; 0   
157AND EXISTS(SELECT 1 FROM @C AS C INNER JOIN @A AS A ON A.ID = C.ID   
158WHERE C.V &gt; (C.P1+C.P2+C.P3+C.P4) AND C.ID &gt;= @CID   
159AND (B.</d.id>