27,579
社区成员
发帖
与我相关
我的任务
分享
create table tb(ID int,parentid int, name varchar(10) ,cnt int)
insert into tb values(1 , 0 , '名称1' , 1)
insert into tb values(2 , 1 , '名称2' , 1)
insert into tb values(3 , 1 , '名称3' , 1)
insert into tb values(4 , 2 , '名称4' , 1)
insert into tb values(5 , 2 , '名称5' , 1)
insert into tb values(6 , 3 , '名称6' , 1)
insert into tb values(7 , 0 , '名称7' , 1)
insert into tb values(8 , 7 , '名称8' , 1)
insert into tb values(9 , 7 , '名称9' , 1)
go
--创建临时表
create table tmp (name varchar(10) ,cnt int)
go
--创建查询指定节点及其所有子节点的函数
create function f_cid(@ID int) returns @t_level table(id int , level int)
as
begin
declare @level int
set @level = 1
insert into @t_level select @id , @level
while @@ROWCOUNT > 0
begin
set @level = @level + 1
insert into @t_level select a.id , @level
from tb a , @t_Level b
where a.parentid = b.id and b.level = @level - 1
end
return
end
go
--创建存储过程并将数据插入临时表
create proc my_proc
as
begin
declare @id as int
declare @cnt as int
declare @name as varchar(10)
set @id = 0
while exists(select 1 from tb where id > @id)
begin
set @id = (select min(id) from tb where id > @id)
set @name = (select name from tb where id = @id)
set @cnt = (select sum(cnt) from (select a.* from tb a , f_cid(@id) b where a.id = b.id ) t)
insert into tmp select @name , @cnt
end
end
go
exec my_proc
select * from tmp
drop table tb , tmp
drop function f_cid
drop proc my_proc
/*
name cnt
---------- -----------
名称1 6
名称2 3
名称3 2
名称4 1
名称5 1
名称6 1
名称7 3
名称8 1
名称9 1
(所影响的行数为 9 行)
*/
CREATE TABLE [dbo].[test] (
[编码] [char] (4) ,
[名称] [char] (20),
[金额] [decimal](9, 2))
GO
insert into test
select '1000 ','一、可控成本',0 union all
select '1100 ',' (1)、原材料',0 union all
select '1101 ',' 钢材',100 union all
select '1102 ',' 铜材',200 union all
select '1103 ',' 铝材',300 union all
select '1200 ',' (2)、辅助材料',0 union all
select '1201 ',' 轴承',400 union all
select '1202 ',' 润滑油',500 union all
select '1300 ',' (3)、燃料动力',0 union all
select '1301 ',' 电费',400 union all
select '1302 ',' 水费',500 union all
select '1303 ',' 水费',500 union all
select '2000 ','二、固定成本',0 union all
select '2100 ',' 人工',600 union all
select '2101 ',' 折旧',700 union all
select '2102 ',' 其它',200 union all
select '0000 ','总成本',0
go
update a set 金额=(select SUM(金额) from test where LEFT(编码,2)=LEFT(a.编码,2)) from test a where a.编码 like '%00'
go
update a set 金额=(select SUM(金额) from test where LEFT(编码,1)=LEFT(a.编码,1) and 编码 like '__00') from test a where a.编码 like '_000'
go
update test set 金额=(select SUM(金额) from test where 编码 like '_000') where 编码='0000'
go
select * from test
/*
编码 名称 金额
---- -------------------- ---------------------------------------
1000 一、可控成本 2900.00
1100 (1)、原材料 600.00
1101 钢材 100.00
1102 铜材 200.00
1103 铝材 300.00
1200 (2)、辅助材料 900.00
1201 轴承 400.00
1202 润滑油 500.00
1300 (3)、燃料动力 1400.00
1301 电费 400.00
1302 水费 500.00
1303 水费 500.00
2000 二、固定成本 1500.00
2100 人工 1500.00
2101 折旧 700.00
2102 其它 200.00
0000 总成本 4400.00
(17 行受影响)
*/
go
drop table test