RaqForum 41 No.
4 View  • 
     ...

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


 
 •  4 View  •