An interesting SQL Server-level summary data issue

Look at the SQL Server Big v Song blog post and discover an interesting SQL Server-level summary data issue. The specific questions are as follows: parent_id emp_id emp_name total_amout
NULL 2 Andrew 200
2 1 Nancy 100
2 3 Janet 120
3 4 Michael 80
1 5 Robert 50
Total sales per employee = own sales + total sales of their subordinates,
Like what:
Andrew = 200_100_120_80_50=550
Nancy = 100+50=150
Janet = 120+80=200
Michael = 80
Robert = 50
How can this SQL query get, ask everyone??? From the data in the data table and the problem, it can be determined that the data table is a parent-child hierarchy type data table, which is a more common type of latitude: parent-child latitude. From the interpretation of the name is a self-referencing data table, the most familiar organization is the structure of this hierarchy, in which different levels of institutions have common characteristics.              This hierarchy is shown in the following: from Baidu query get.        It seems that the solution to the problem (CTE recursive query + cursor), there are other bloggers comments (some of the expense data table design is not perfect, but also through the virtual hierarchy of string columns to achieve, and to get the current level of all levels below the summary encapsulated as storage, etc.).    In order to achieve this problem, I use the CTE recursive query +apply, the specific implementation ideas are as follows: 1, the CTE recursive query virtual out of several columns, there is a hierarchical Index string column (the column represents a hierarchical identity ID string format, easy to find).   2, use apply to achieve summary data (of course, you can also use SELECT + subquery). The implementation code for the specific demo is as follows:

IF object_id (N ' dbo. Myemp ', N ' U ') is not nullbegin DROP TABLE dbo. Myemp; Endgocreate TABLE dbo. Myemp (myempid int not null, ParentID int NULL, Myempname NVARCHAR () is not NULL, hourssalary int is not NULL); GOif object_id (n ' pk_u_cl_myemp_myempid ', n ' PK ') is Nullbegin ALTER TABLE [dbo]. [Myemp] ADD CONSTRAINT [pk_u_cl_myemp_myempid] PRIMARY KEY CLUSTERED ([myempid] ASC) with (Pad_index = OFF, statis  Tics_norecompute = off, sort_in_tempdb = off, Ignore_dup_key = off, ONLINE = off, Allow_row_locks = on, Allow_page_locks = On, FILLFACTOR = +) on [PRIMARY]; Endgoif object_id (n ' fk_myemp_myemp_parentid ', n ' F ') is Nullbegin ALTER TABLE dbo. Myemp ADD CONSTRAINT fk_myemp_myemp_parentid FOREIGN KEY (parentid) REFERENCES dbo. Myemp (MYEMPID); endgo--Insert Test datainsert into dbo.  Myemp (Myempid, ParentID, Myempname, Hourssalary) VALUES (1, NULL, n ' Andrew ', $), (2, 1, n ' Nancy ', +), (3, 1, n ' Janet ', (4, 3, n ' Michael '), (5, 2, n ' Robert ') GO; With Tdata (MYempid, Myempname, ParentID, ParentName, Hourssalary, Parenthierarchyindex, Hierarchyindex, LevelID, HierarchyName,                                                                                                HierarchyName2) as (--Datum point query SELECT myempid                                                                                                /* Employee id*/, Myempname                                                                        /* Employee Name */, ISNULL (ParentID, 0) as ParentID                                                                /* Parent Employee id*/, CAST (N ' as NVARCHAR ()) as ParentName                                                                                            /* Parent Employee Name */, Hourssalary                    /* Hourly pay */, CAST (CONCAT (', ', ISNULL (parentid, 0), ', ') as VARCHAR) as Parenthierarchyindex /* Parent-level Index String */, CAST (CONCAT (', ', ISNULL (parentid, 0), ', ', Myempid, ', ') as VARCHAR) as Hierar Chyindex/* Hierarchical Index string strings, including current level */, CAST (1 as INT) as Levelid                                                        /* level ID, root level is 1, the deeper the level the number is larger */, CAST (Myempname as NVARCHAR ()) as Hierarchyname                                                        /* Hierarchy name, tree-shaped structure display */, CAST (Myempname as NVARCHAR) as HierarchyName2 /* Level name 2, horizontal structure display */FROM dbo.        Myemp WHERE ParentID is NULL--recursive query UNION all SELECT t.myempid, T.myempname, T.parentid , T2. Myempname, T.hourssalary, CAST (CONCAT (T2. Parenthierarchyindex, T.parentid, ', ') as VARCHAR) as Parenthierarchyindex, CAST (CONCAT (T2. Hierarchyindex, T.myempid, ', ') as VARCHAR) as Hierarchyindex, T2.    Levelid + 1 as Levelid, CAST (CONCAT (REPLICATE (N ' | ', T2. Levelid), T.myempname) as NVARCHAR () as Hierarchyname, CAST (CONCAT (T2). HierarchyName2, '-i ', t.myempname) as NVARCHAR) as HierarchynamE2 from dbo. Myemp as T INNER JOIN tdata as T2 on t.parentid = T2. MYEMPID)--using Hierarchyindex to achieve--cross applyselect t.*, T2. Totalsalary as Totalsalaryfrom tdata as T cross APPLY (SELECT SUM (tdata.hourssalary) as totalsalary from Tdata WH ERE hierarchyindex like CONCAT (t.hierarchyindex, '% ')) as T2order by T.hierarchyindex asc;--SELECT + subquery--select t.*, Tot Alsalary = (SELECT SUM (tdata.hourssalary) from Tdata WHERE tdata.hierarchyindex like CONCAT (t.hierarchyindex, '% '))--fro M Tdata as T--order by T.hierarchyindex asc;--uses parenthierarchyindex--cross apply--select t.*, T.hourssalary + T2. Downmembertotalhourssalary as Totalsalary--from tdata as t--cross APPLY (--SELECT ISNULL (SUM (tdata.hourssalary ), 0) as downmembertotalhourssalary--from tdata--WHERE tdata.parenthierarchyindex like CONCAT (t.parenthier Archyindex, T.myempid, '% ')--as T2--order by T.hierarchyindex asc;--SELECT + subquery--select t.*, T.hourssalary + (Sele CT IsnuLL (SUM (tdata.hourssalary), 0) as downmembertotalhourssalary--from tdata--WHERE tdata.parenthierarchyindex Like CONCAT (T.parenthierarchyindex, t.myempid, '% ')) as Totalsalary--from Tdata as T--order by T.hierarchyindex ASC; GO

　　

The above solution is implemented without modifying data structure, from the above solution, we can start with the design of the data table, the virtual parent-level index string column is added to the data table, the column is created as a clustered index, to improve query performance. Add a new column to the T-SQL script as follows:

IF not EXISTS (SELECT 1 from sys.columns WHERE object_id = object_id (N ' dbo. Myemp ', n ' U ') and name = N ' hierarchyindex ') BEGIN    ALTER TABLE dbo. Myemp ADD hierarchyindex VARCHAR (+) not NULL CONSTRAINT df_myemp_hierarchyindex DEFAULT '; Endgo

If the column is created as clustered and unique, the corresponding T-SQL script is as follows:

--Delete the foreign key if OBJECT_ID (n ' fk_myemp_myemp_parentid ', n ' F ') is not nullbegin ALTER TABLE dbo. Myemp DROP CONSTRAINT Fk_myemp_myemp_parentid; endgo--Delete primary key if OBJECT_ID (n ' pk_u_cl_myemp_myempid ', n ' PF ') is Nullbegin ALTER TABLE dbo. Myemp DROP CONSTRAINT pk_u_cl_myemp_myempid; endgo--created (unique: Semantic analysis obtained, not using the Create unique keyword) clustered index if not EXISTS (SELECT 1 from sys.indexes WHERE object_id = object_id (N ' dbo. Myemp ', n ' U ') and name = N ' ix_u_cl_myemp_hierarchyindex ') BEGIN CREATE CLUSTERED INDEX Ix_u_cl_myemp_hierarchyindex on D Bo. Myemp (Hierarchyindex ASC) with (Pad_index = off, Statistics_norecompute = off, sort_in_tempdb = off, drop_ EXISTING = off, ONLINE = off, Allow_row_locks = on, Allow_page_locks = on, FILLFACTOR = All) endgo--Create primary key and nonclustered index if OBJECT_ID ( N ' pk_u_cl_myemp_myempid ', n ' PK ') is Nullbegin ALTER TABLE [dbo]. [Myemp] ADD CONSTRAINT [pk_u_ncl_myemp_myempid] PRIMARY KEY nonclustered ([myempid] ASC) with (Pad_index = OFF, ST Atistics_norecompute = OFF, sort_in_tempdb = ofF, Ignore_dup_key = off, ONLINE = off, Allow_row_locks = on, Allow_page_locks = on, FILLFACTOR = n) on [PRIMARY]; endgo--creates a foreign key if OBJECT_ID (n ' fk_myemp_myemp_parentid ', n ' F ') is Nullbegin ALTER TABLE dbo. Myemp ADD CONSTRAINT fk_myemp_myemp_parentid FOREIGN KEY (parentid) REFERENCES dbo. Myemp (MYEMPID); Endgo

The T-SQL for synchronizing n ' hierarchyindex field column values is as follows:

; With Tdata (Myempid, Hierarchyindex) as (    --Reference point query    SELECT myempid, CAST (CONCAT (', ', ISNULL (parentid, 0), ', ', Myem PID, ', ') as VARCHAR) as Hierarchyindex from    dbo. Myemp    WHERE ParentID is NULL    --recursive query    UNION all    SELECT t.myempid, CAST (CONCAT (T2. Hierarchyindex, T.myempid, ', ') as VARCHAR) as Hierarchyindex from    dbo. Myemp as T/* child table */        INNER JOIN tdata as T2/* Parent table */on            T.parentid = T2. Myempid)--select t.*update t2set T2. Hierarchyindex = T.hierarchyindexfrom Tdata as T    INNER JOIN dbo. Myemp as T2 on        t.myempid = T2. Myempidwhere T2. Hierarchyindex = "; GO

Verify that the data has been modified using the following T-sql:

SELECT myempid, ParentID, Myempname, Hourssalary, Hierarchyindexfrom dbo. Myemp; GO

The T-SQL statements for the solution to the problem are as follows:

SELECT t.myempid, T.parentid, T.myempname, T.hourssalary, T.hierarchyindex, T2. Totalsalary as Totalsalaryfrom dbo. Myemp as T cross    APPLY (SELECT SUM (hourssalary) as totalsalary from dbo. Myemp WHERE hierarchyindex like CONCAT (t.hierarchyindex, '% ')) as T2; GO

　　

An interesting SQL Server-level summary data issue