表结构
炉号 炉次变更 料成本
A B a
B C b
C c
D C d
E e即炉次变更为A->B->C,D->C,E,求没有炉次变更的炉号的料成本。
怎么用Sql语句得到表:
炉号 炉次变更 料成本
C a+b+c+d
E e
请求赐教这一SQL语句,谢谢!急!在线等待!
炉号 炉次变更 料成本
A B a
B C b
C c
D C d
E e即炉次变更为A->B->C,D->C,E,求没有炉次变更的炉号的料成本。
怎么用Sql语句得到表:
炉号 炉次变更 料成本
C a+b+c+d
E e
请求赐教这一SQL语句,谢谢!急!在线等待!
returns varchar(100)
as
begin
declare @name varchar(8000)
set @name=''
select @name=@name+'+'+料成本 from t where bh=@bh
return stuff(@name,1,1,'')
end
gocreate table test(炉号 varchar(10),炉次变更 varchar(10),料成本 varchar(10))
insert test select 'A','B','a'
union all select 'B','C','b'
union all select 'B','','c'
union all select 'D','C','d'
union all select 'E','','e'
select * into t from
(
select 炉号,炉次变更,bh=case when 炉号<='C' or 炉号='D' then 1 else 2 end,料成本 from test
)aselect distinct 炉号,炉次变更,料成本=dbo.fun(bh) from t
where 炉次变更=''drop table test,t
drop function fun炉号 炉次变更 料成本
---------- ---------- ------------
B a+b+c+d
E e(所影响的行数为 2 行)
returns varchar(100)
as
begin
declare @name varchar(8000)
set @name=''
select @name=@name+'+'+料成本 from t where bh=@bh
return stuff(@name,1,1,'')
end
gocreate table test(炉号 varchar(10),炉次变更 varchar(10),料成本 varchar(10))
insert test select 'A','B','a'
union all select 'B','C','b'
union all select 'C','','c'
union all select 'D','C','d'
union all select 'E','','e'
select * into t from
(
select 炉号,炉次变更,bh=case when 炉号<='C' or 炉号='D' then 1 else 2 end,料成本 from test
)aselect distinct 炉号,炉次变更,料成本=dbo.fun(bh) from t
where 炉次变更=''drop table test,t
drop function fun炉号 炉次变更 料成本
---------- ---------- ------------
C a+b+c+d
E e(所影响的行数为 2 行)
炉号 炉次变更 料成本
A B a
B C b
表示A炉钢水料成本为a,倒入B炉中(炉次变更),B炉料成本为b(A炉未倒入B炉前,倒入后B炉成本为a+b;
create table test(炉号 varchar(10),炉次变更 varchar(10),料成本 varchar(10))
insert test select 'A','B','a'
union all select 'B','C','b'
union all select 'C','T','c'
union all select 'D','C','d'
union all select 'E','','e'
union all select 'T','','e'--select * from testcreate function fun_j(@id varchar(20))
returns varchar(1000)
as
begin
declare @lh varchar(100)
set @lh=''
declare @lh_i table(炉号 varchar(20))
declare @lh_j table(炉号 varchar(20))
insert into @lh_i
select 炉号 from test where 炉次变更=@idselect @lh=@lh+料成本 from test where 炉次变更=@idwhile exists (select a.炉号 from test a, @lh_i b where a.炉次变更=b.炉号 )
begin
insert into @lh_j
select a.炉号 from test a, @lh_i b where a.炉次变更=b.炉号 select @lh=@lh+a.料成本 from test a, @lh_i b where a.炉次变更=b.炉号 delete @lh_i
insert into @lh_i select 炉号 from @lh_j
delete @lh_j
endreturn @lh
end select 炉号,炉次变更 ,dbo.fun_j(炉号)+料成本 as 料成本 from test where isnull(炉次变更,'')=''/*炉号 炉次变更 料成本
---------- ---------- ------------
E e
T cbdae(所影响的行数为 2 行)
*/