12、 将表TEST
SEQ QTY IO
1 100 1
2 120 1
3 130 1
4 60 -1
5 90 -1 转换成表
Seq qty Left
1 100 0
2 120 70
3 130
13、 有表TEST
ID FATHER CHIELD
1 A A1
2 A A2
3 A B
4 B B1
5 B B2
转成表
COLDE CLASS ISEND
A 1 0
B 2 0
A1 2 1
A2 2 1
B1 3 1
B2 3 1
两道难题谢谢大家了!
SEQ QTY IO
1 100 1
2 120 1
3 130 1
4 60 -1
5 90 -1 转换成表
Seq qty Left
1 100 0
2 120 70
3 130
13、 有表TEST
ID FATHER CHIELD
1 A A1
2 A A2
3 A B
4 B B1
5 B B2
转成表
COLDE CLASS ISEND
A 1 0
B 2 0
A1 2 1
A2 2 1
B1 3 1
B2 3 1
两道难题谢谢大家了!
create table test(seq int ,qty int, [io] int)
insert into test select 1,100,1
insert into test select 2,120,1
insert into test select 3,130,1
insert into test select 4,60,-1
insert into test select 5,90,-1select seq,qty,
case when isnull((select sum(qty) from test where [io]=1 and seq<=A.seq),0)<=isnull((select sum(qty) from test where [io]=-1),0)
then 0
else case when isnull((select sum(qty) from test where [io]=1 and seq<A.seq),0)>=isnull((select sum(qty) from test where [io]=-1),0)
then null
else isnull((select sum(qty) from test where [io]=1 and seq<=A.seq),0)-isnull((select sum(qty) from test where [io]=-1),0)
end
end as [left]
from test A
where [io]=1
/*
seq qty left
----------- ----------- -----------
1 100 0
2 120 70
3 130 NULL
*/
drop table test
比如
A A1
A A2
A B
B B1
B B2
A1 A11
A11 B1這樣,B1的class 有3,4
二:
create table tm(ID INT,father varchar(10),chield varchar(10))
insert into tm select 1,'A','A1'
insert into tm select 2,'A','A2'
insert into tm select 3,'A','B'
insert into tm select 4,'B','B1'
insert into tm select 5,'B','B2'
go create proc wsp
as
declare @t table(COLDE varchar(10),class int,ISEND int)
declare @chield varchar(10)
insert into @t select distinct father,1,0 from tm a where not exists(select 1 from tm where tm.chield=a.father)
declare cur cursor for select chield from tm where father in(select distinct father from tm a where not exists(select 1 from tm where tm.chield=a.father))
open cur
fetch next from cur into @chield
while(@@fetch_status=0)
begin
if exists(select 1 from tm where father=@chield)
begin
insert into @t select @chield,2,0
end
else
begin
insert into @t select @chield,2,1
end
insert into @t select chield,3,1 from tm where father = @chield
fetch next from cur into @chield
end
close cur
deallocate cur
select * from @t order by class,isend,colde
exec wsp