SetANSI_NULLSONsetQUOTED_IDENTIFIERONgo -- thisdocumentdesignbyzzl ---- function: totalaboutatreestructure -- author: Lori. zhang, zzl -- displayview: a13 -- a112 -- a1111 ↓ ALT
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 ---------------------------------------------- ALT
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] -- Obtain the summary and
@ Taskid int
As
Declare @ level_tt table (taskid nvarchar (1000), parentID nvarchar (1000), level int) -- declare a table change, ID, parent ID, and level
Declare @ level int
Set @ level = 0
Insert @ level_tt (taskid, parentID, level) -- insert it to the table variable @ level_tt and set all records whose parent ID is null or 0.
Select taskid, parentID, @ level from task where isnull (parentID, '') =''
While @ ROWCOUNT> 0 -- when such a record exists
Begin
Set @ level = @ level + 1 -- level change auto-increment 1
Insert @ level_tt (taskid, parentID, level) -- insert to table change @ level_tt, and set the parent ID of all task tables to the ID of the @ level_tt table and its level-1 record.
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 -- display cascade structure
-- The execution result of the above program is:
-- Taskid parentid level
-- 1 0 0
-- 2 01 1
-- 3 01 1
-- 4 012 2
-- From the preceding results, we can see that there is a relationship between 1, 2, and 4 in the table. They are tree results. If you want to calculate the result of 1, you need to add the results of 2 and 4.
Declare @ tmp table (taskid int, parentid int, total float, moneyTotal float) -- Define table change @ tmp
Insert into @ tmp -- insert to the @ tmp table. From the task table and the @ level_tt table, the summation object is realmoney. If realmoney is empty, set it to 0 and the summation condition is
Select. taskid,. parentID, SUM (isnull (c. realwork, 0) as total, SUM (isnull (c. realmoney, 0) as moneytotal -- parentid is parentid %, with wildcards
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 -- if no record is found, insert an empty record to the temporary table.
Begin
Insert into @ tmp (taskid, parentid, total, moneytotal) values (,) -- if no record exists, insert a 0 record
End
Select total, moneytotal from @ tmp -- select temporary table