SQL Multi-Level Classification summary implementation

This article introduces the implementation method and data structure of SQL multi-level classified summary. If you have any problems, refer to it.

This article introduces the implementation method and data structure of SQL multi-level classified summary. If you have any problems, refer to it.

The database structure is as follows:
Category Table
CATEGORY id upper-level category id classification name classification level sorting Value

The Code is as follows:
Id parentid categoryname categorylevel ordering 1 null c1 1 1 1 2 1 c11 2 1 3 1 c12 2 2 2 4 1 c13 2 3 5 1 c14 2 4 6 2 c111 3 1 7 2 c112 3 2

Then the content table is
Content id category id .........

The Code is as follows:
Id categoryid ......... 1 ........ 2 4 ........ 3 5 ........

The disadvantage of this solution is that, if the data volume is large and there are many sub-categories and the number of sub-categories reaches 4 or above, this method occupies the connection pool.
It has a great impact on performance.

If you use the SQL CTE for Recursive processing, you can query the results at one time, and the performance is very good.
Compared with program processing (when the data volume is large), temporary tables provide better performance and convenience.

The Code is as follows:
With area ( Select *, id px, cast (id as nvarchar (4000) px2 from region where parentid = 0 Union all Select a. *, B. px, B. px2 + ltrim (a. region_id) from region a join area B on a. parentid = B. id ) Select * from area px, px2

Query results-subcategories under all categories and corresponding categories

The Code is as follows:
Id title parentid 1 Guangdong 0 2 Guangzhou 1 3 Baiyun District 2 4 Shenzhen 1 5 Hunan 0 6 Changsha 5 7. Zhuzhou 5

The Code is as follows:
With area ( Select * from region where parentid = 1 Union all Select a. * from region a join area B on a. parentid = B. id ) Select * from area

Query results-specify the category and subcategory under the corresponding category
Id title parentid
1 Guangdong 0
2 Guangzhou 1
3 Baiyun District 2

Implementation Program

The Code is as follows:

/* Title: Query functions of a specified node and all its subnodes Author: AI xinjue Luo. Xin Hua) Time: 2008-05-12 Location: Shenzhen, Guangdong Province */ Create table tb (id varchar (3), pid varchar (3), name varchar (10 )) Insert into tb values ('001', null, 'guangdong province ') Insert into tb values ('002 ', '001', 'guangzhou ') Insert into tb values ('003 ', '001', 'shenzhen City ') Insert into tb values ('004 ', '002', 'tianhe district ') Insert into tb values ('005 ', '003', 'luohu ') Insert into tb values ('006 ', '003', 'futian district ') Insert into tb values ('007 ', '003', 'baoan district ') Insert into tb values ('008 ', '007', 'West township ') Insert into tb values ('009', '007 ', 'longhua Zhen ') Insert into tb values ('010 ', '007', 'songgang town ') Go -- Query functions of a specified node and all its subnodes Create f_cid (@ ID varchar (3) returns @ t_level table (id varchar (3), 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. pid = B. id and B. level = @ level-1 End Return End Go -- Call function query 001 (Guangdong) and all its subnodes Select a. * from tb a, f_cid ('001') B where a. id = B. id order by a. id /* Id pid name ------------------ 001 NULL Guangdong Province 002 001 Guangzhou 003 001 Shenzhen 004 002 Tianhe District 005 003 Luohu District 006 003 Futian District 007 003 Baoan District 008 007 West township 009 007 Longhua town 010 007 songgang town (The number of affected rows is 10) */ -- Call function query 002 (Guangzhou City) and all its subnodes Select a. * from tb a, f_cid ('002 ') B where a. id = B. id order by a. id /* Id pid name ------------------ 002 001 Guangzhou 004 002 Tianhe District (The number of affected rows is 2) */ -- Call function query 003 (Shenzhen City) and all its subnodes Select a. * from tb a, f_cid ('003 ') B where a. id = B. id order by a. id /* Id pid name ------------------ 003 001 Shenzhen 005 003 Luohu District 006 003 Futian District 007 003 Baoan District 008 007 West township 009 007 Longhua town 010 007 songgang town (The number of affected rows is 7) */ Drop table tb Drop function f_cid

Instance 2

The Code is as follows:

T1 Id parentid M N E m F m X f Y f Z B T2 Row id amount 1 a 13.00 2 B 20.00 3 e 20.00 4 f 20.00 5x20.00 6 y 20.00 7 z 20.00 8 e 12.00 9x11.00 10 f 13.00 How to get the following results: Row id amount 7x20.00 11x11.00 X subtotal 31.00 8 y 20.00 Y 20.00 6 f 20.00 12 f 13.00 F subtotal 84.00 5 e 20.00 10 e 12.00 E subtotal 32.00. 3 m 14.00 M subtotal 130.00 4 n 13.00 N subtotal 13.00 1 a 13.00 A. Subtotal 156.00 9 z 20.00 Z subtotal 20.00 2 B 20.00 Subtotal B 40.00 Total 196.00 Implementation Program -- Sample Data Create table t1 ( Id char (1 ), Parentid char (1) ); INSERT t1 SELECT 'M', 'A' UNION ALL SELECT 'n', 'A' UNION ALL SELECT 'E', 'M' UNION ALL SELECT 'F', 'M' UNION ALL SELECT 'x', 'F' UNION ALL SELECT 'y', 'F' UNION ALL SELECT 'Z', 'B '; Create table t2 ( Row int, Id char (1 ), Amount decimal (10, 2) ); INSERT t2 SELECT '1', 'A', '13. 00' UNION ALL SELECT '2', 'B', '20. 00' UNION ALL SELECT '3', 'E', '20. 00' UNION ALL SELECT '4', 'F', '20. 00' UNION ALL SELECT '5', 'x', '20. 00' UNION ALL SELECT '6', 'y', '20. 00' UNION ALL SELECT '7', 'z', '20. 00' UNION ALL SELECT '8', 'E', '12. 00' UNION ALL SELECT '9', 'x', '11. 00' UNION ALL SELECT '10', 'F', '13. 00 '; GO -- Statistics -- Level-by-level Summary Declare @ l int Set @ l = 1 Select A. [id], [Pid] = A. parentid, [Sumnum] = SUM (B. amount ), Level = case When exists (select * from t1 where parentid = a. [id]) Then @ L-1 else @ l end Into [#] From t1 Left join t2 B On a. id = B. id Group by a. id, A. parentid; If @ row/42852.htm target = _ blank> count> 0 Create index IDX _ # _ id_pid on [#] ([id], [pid]) Else Set @ l = 999 While @ rowcount> 0 or @ l = 1 Begin Set @ l = @ l + 1 Update a set level = @ l, [sumnum] = isnull (a. [sumnum], 0) + isnull (B. [sumnum], 0) From [#] ,( Select aa. pid, [sumnum] = sum (aa. [sumnum]) From [#] aa ,( Select distinct [pid] from [#] Where level = L-1 ) Bb where aa. [pid] = bb. [pid] And not exists ( SELECT * FROM [#] WHERE [PID] = aa. [PID] AND [Level] = 0) Group by aa. [PID] Having sum (case when aa. level = 0 then 1 else 0 end) = 0 ) B where a. [id] = B. [pid] End -- Final Result SELECT Row = CASE When grouping (A. row) = 0 then rtrim (A. row) ELSE n'' END, Id = CASE When grouping (A. row) = 0 then a. id When grouping (A. id) = 0 then a. id + 'subtotal' Else n 'Total' END, Amount = CASE When grouping (A. row) = 0 then sum (A. amount) When grouping (A. id) = 0 then isnull (select sum (B. sumnum) FROM # B where a. id = B. id), SUM (A. amount )) Else sum (A. amount) END FROM t2 Group by a. id, A. row with rollup; Drop table [#] GO Drop table t1, t2; /* -- Result Row id amount -------------------------------------------------------- 1 a 13.00 A. Subtotal 13.00 2 B 20.00 Subtotal B 20.00 3 e 20.00 8 e 12.00 E subtotal 32.00. 4 f 20.00 10 f 13.00 F subtotal 84.00 5x20.00 9x11.00 X subtotal 31.00 6 y 20.00 Y 20.00 7 z 20.00 Z subtotal 20.00 Total 169.00 (18 rows affected) --*/

Performance analysis:
For a data table with 3500 region records, there are three levels: province, city, and county.
It takes 1 second to query, and it seems a little slow visually, but it does not affect
Classification with a small amount of data, absolutely no pressure on Use