set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- this document design by zzl --
-- function : total about a tree structure
-- author : Lori.zhang ,zzl
-- display view : a1   3
--                a11  2
--                a111 1
------------------------------------------
ALTER  proc [dbo].[zzl_task_tree_total] --通过指定的taskid,来获取它和它下面的所有子元素的汇总和
@taskid int
as
declare @level_tt table(taskid nvarchar(1000),parentID nvarchar(1000),level int) --声明一个表变更,ID,上级ID,及层次
declare @level int
set @level=0
insert @level_tt(taskid,parentID,level) --插入到表变量@level_tt,将所有上级ID为null或是为0的记录
select taskid,parentID,@level from task where isnull (parentID,'')=''
while @@ROWCOUNT>0 --当存在这样的记录时
begin
set @level=@level+1 --层次变更自加1
insert @level_tt(taskid,parentID,level) --插入到表变更@level_tt,将所有的task表的上级ID等于@level_tt表的ID并且是它的低一级的记录
select a.taskid,cast(b.parentID as varchar)+cast(a.taskid as varchar),@level
from task a,@level_tt b
where a.parentID=b.taskid and b.level=@level-1
end
select * from @level_tt  --显示级联的结构
-- 上面程序执行的结果为:
-- taskid  parentid  level
-- 1   0    0
-- 2   01    1
-- 3   01    1
-- 4         012         2
-- 从上面的结果可以看到,表中的1,2,4有着关系,他们是树型结果的,如果想求1的结果,需要将2和4的结果相加
declare @tmp table(taskid int,parentid int,total float,moneyTotal float) --再定义表变更@tmp
insert into @tmp --插入到@tmp表,从task表,@level_tt表,求和对象为realmoney,当realmoney为空时,把它设为0,求和条件为
select a.taskid,a.parentID,SUM(isnull(c.realwork,0)) as total,SUM(isnull(c.realmoney,0)) as moneytotal --parentid为parentid%,使用通配符
from task a,@level_tt b,Task c,@level_tt d
where (a.taskid=b.taskid and
c.taskid=d.taskid
and d.parentID like b.parentID+'%' )
and (a.taskid=@taskid)
group by a.taskid,a.parentID
order by a.parentID

--select @total=total from @tmp
if @@rowcount=0  --如果没有找到记录,就向临时表插入空记录
begin
insert into @tmp(taskid,parentid,total,moneytotal)values(0,0,0,0) --如果没有记录,就插入一个0记录
end
select total,moneytotal from @tmp --选择临时表