SQL Server tree summation

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.