一句话更新树表的PATH字段，更新树表的路径

熊一族

于 2023-04-27 11:52:17 发布

阅读量283

点赞数

文章标签：数据库 sql mysql

本文为博主原创文章，未经博主允许不得转载。

本文链接：https://blog.youkuaiyun.com/FlowerDance17/article/details/130403467

版权

mysql 表t_hrms_hr_organization 中有id_,parent_org,id_path_字段，其中parent_org表示父节点的ID

id_path_中存入节点路径，类似于 /1/2/3 1是根节点，2是子节点，3是孙子节点

UPDATE t_hrms_hr_organization o
JOIN (
  SELECT id_, parent_org, CONCAT('/', id_) AS id_path_
  FROM t_hrms_hr_organization
  WHERE parent_org IS NULL

  UNION ALL

  SELECT o.id_, o.parent_org, CONCAT(p.id_path_,'/',o.id_  ) AS id_path_
  FROM t_hrms_hr_organization o
  JOIN t_hrms_hr_organization p ON o.parent_org = p.id_
) q ON o.id_ = q.id_
SET o.id_path_ = q.id_path_;