27,579
社区成员
发帖
与我相关
我的任务
分享
declare @t table([编码] [char](4),[名称] [char](20),[金额] [decimal](9,2))
insert into @t
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
;with cte as(select SUBSTRING([编码],1,2) [编码],max(cast([编码] as varbinary)) px from @t group by SUBSTRING([编码],1,2))
select a.*,px=(
(select px from cte where 编码=LEFT(a.[编码],2))+cast(a.[编码] as varbinary))
from @t a order by px
(17 行受影响)
(17 行受影响)
0000 总成本 0.00 0x3030303030303030
1000 一、可控成本 0.00 0x3130303031303030
1100 (1)、原材料 0.00 0x3131303331313030
1101 钢材 100.00 0x3131303331313031
1102 铜材 200.00 0x3131303331313032
1103 铝材 300.00 0x3131303331313033
1200 (2)、辅助材料 0.00 0x3132303231323030
1201 轴承 400.00 0x3132303231323031
1202 润滑油 500.00 0x3132303231323032
1300 (3)、燃料动力 0.00 0x3133303331333030
1301 电费 400.00 0x3133303331333031
1302 水费 500.00 0x3133303331333032
1303 水费 500.00 0x3133303331333033
2000 二、固定成本 0.00 0x3230303032303030
2100 人工 600.00 0x3231303232313030
2101 折旧 700.00 0x3231303232313031
2102 其它 200.00 0x3231303232313032
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 行)
*/