22,210
社区成员
发帖
与我相关
我的任务
分享
select id=identity(int,1,1),part,hou,kuan=60,priority=0,flag=0 into #tmp from T
--==>>>>
-- kuan栏位的数据类型换一下
select id=identity(int,1,1),part,hou,kuan=60.0 ,priority=0,flag=0 into #tmp from T
--数据不多的话,试试看游标能不能跑。
Create table T(part varchar(10), hou numeric(18,2), kuan numeric(18,2))
insert into T
select 'F042', 2.5, 5.6
union all select 'F042', 2.5, 5.6
union all select 'F042', 2.5, 5.6
union all select 'F042', 2.5, 5.6
union all select 'W001',2.5,10
union all select 'W001',2.5,10
union all select 'W001',2.5,20
union all select 'W001',2.5,20
union all select 'W001',2.5,25
union all select 'W001',2.5,25
union all select 'W001',2.5,25
union all select 'W001',2.5,25
union all select 'W001',2.5,25
union all select 'W001',2.5 ,6.3
union all select 'W001',2.5, 6.3
union all select 'W001',2.5 ,6.3
GO
select id=identity(int,1,1),part,hou,kuan,number=0 into #T from T order by part,hou,kuan desc
select id=identity(int,1,1),part,hou,kuan=60,priority=0,flag=0 into #tmp from T
declare @part varchar(10),@hou numeric(18,2), @kuan numeric(18,2) , @use numeric(18,2),@left numeric(18,2),@id1 int,@id2 int
declare c1 cursor for
select id,part,hou,kuan from #T order by part,hou,kuan desc
open c1
fetch next from c1 into @id1,@part,@hou,@kuan
while @@fetch_status=0
begin
select @left=kuan-@kuan, @id2 =id
from (select top 1 * from #tmp where part=@part and hou=@hou and kuan>=@kuan order by priority desc,kuan,id ) A
update #T set number=@id2 where id=@id1
Update #tmp set kuan=@left,priority=1 where id=@id2
fetch next from c1 into @id1,@part,@hou,@kuan
end
close c1
deallocate c1
GO
-- number栏位指是否用同一块60的半成品
select * from #T order by number
/*
id part hou kuan number
----------------------------------------------------
1 F042 2.50 5.60 1
2 F042 2.50 5.60 1
3 F042 2.50 5.60 1
4 F042 2.50 5.60 1
5 W001 2.50 25.00 5
6 W001 2.50 25.00 5
12 W001 2.50 10.00 5
13 W001 2.50 10.00 6
7 W001 2.50 25.00 6
8 W001 2.50 25.00 6
9 W001 2.50 25.00 7
10 W001 2.50 20.00 7
14 W001 2.50 6.30 7
15 W001 2.50 6.30 7
16 W001 2.50 6.30 8
11 W001 2.50 20.00 8
*/
GO
drop table T
drop table #T
drop table #tmp