下拉树的sql的修改

Question

下拉树的sql
uuid--字段id；sysname--名称；parentval--父级的字段id
组织树表：SELECT uuid, name,parentval FROM organ;
人员表：SELECT id,station_name,person_id,person_name from person；
关联关系：组织树表.uuid = 人员表.id
求：形成一个新的树表，在组织下展示人员姓名。person_id的父级id是uuid
新表字段sysid ，sysname，sysparentval，

Answer 1

可以用union all和cte完成：

WITH RECURSIVE cte (sysid, sysname, sysparentval) AS (
  SELECT p.person_id, p.person_name, o.parentval
  FROM person p
  JOIN organ o ON o.uuid = p.person_id
  UNION ALL
  SELECT o.uuid, o.name, o.parentval
  FROM organ o
  JOIN cte ON cte.sysparentval = o.uuid
)
SELECT sysid, sysname, sysparentval
FROM cte
ORDER BY sysid;