SQL：Recursive calculation of tree like hierarchical structure

SQL SERVER has a task table where the id field is the primary key and the parent field is the foreign key pointing to the id, used to describe how multiple nodes form a tree. The root node of a tree is null, and the records with a null parent are the child nodes of the root, that is, the secondary nodes. The weight field only has a value when it is a leaf node.

id	name	weight	parent
1	t1	null	null
22	t22	null	1
3	t3	0.03	1
4	t4	0.1	22
55	t55	null	22
6	t6	null	null
7	t7	0.01	6
11	t11	1	55
12	t12	2	55

Now we need to recursively calculate the hierarchical structure of the tree. Parallel nodes are arranged in order of their IDs, and the new name field=level * underline + original name, which is used to visually represent the hierarchical relationship; The new weight field is the sum of the weights of all subordinate nodes, and the level field represents the hierarchy.

name	weight	level
t1	3.13	1
_t3	0.03	2
_t22	3.1	2
__t4	0.1	3
__t55	3	3
___t11	1	4
___t12	2	4
t6	0.01	1
_t7	0.01	2

SPL code:

	A	B
1	=data=MSSQL.query("select * from task order by id")
2	=res=create(name,weight,level)
3	=data.select(!parent).(tree( id, name, weight,1))
4	func tree(I,N,W,L)	=r=res.insert@n(0, fill("_",L-1)+N, 0, L )
5		=data.select(parent==I).sum(tree(id, name, weight, L+1) )
6		return r.weight = W + B5
7	return res

A1: Query the database through JDBC.

A2: Create an empty result set.

A3: Filter out the child nodes of the root and loop through these nodes using recursive function.

A4-B6: Function tree, which adds new records to the result set, recursively processes directly subordinate nodes, and returns the weight of the new record. It has four parameters: id, name, weight, and level.

B4: Add new records to the result set and spell out the new name and level. The new weight will be calculated later, so let's set it to 0 for now.

B5: Filter out the direct subordinate nodes of this node, recursively calculate the weights of these nodes, and sum them up.

B6: Calculate the new weight and modify this record to return the new weight.

A7: Return the result set.

Problem source：https://stackoverflow.com/questions/78409018/create-a-task-sheet-by-cte