乾学院 53 号会员
1 回帖  •  15 浏览  • 
     ...

SQL：递归计算出树型层次结构

 

SQL SERVER 有个任务表，其中 id 字段是主键，parent 字段是指向 id 的外键，用于描述多个节点如何组成一棵树状。树的根节点是空，parent 为空的那些记录是根的子节点，即二级节点。weight 字段只有在叶子节点时才有值。

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

现在要递归计算出树形层次结构，平行节点按 id 顺序排列，新 name 字段 = 层级 * 下划线 + 原 name，用来直观表现层级关系；新 weight 字段 = 对下级所有节点的 weight 求和，level 字段表示层级。

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代码

　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 + B6

7

return res

A1：通过JDBC查询数据库。

A2：创建空的结果集。

A3：筛选出根的子节点，并用递归函数循环处理这些节点。

A4-B6：函数tree，作用向结果集追加新记录，递归处理直属下级节点，返回新记录的weight，有四个参数，id、name、weight、level。

B4：在结果集上追加新记录，拼出新name、新level。新weight要稍后计算，这里先置为0。

B5：筛选出本节点的直属下级节点，递归计算出这些节点的weight，并求和。

B6：计算新weight并修改本记录，返回新weight。

A7：返回结果集。

问题来源：https://stackoverflow.com/questions/78409018/create-a-task-sheet-by-cte