526互联

Oracle递归查询：树结构构建节点的全路径ID

发布时间 2023-09-07 17:36:05作者: 偷师学艺小b仔汁

背景

业务上有个分类表CATY，其中有个全路径ID字段full_id，存放的值是分类节点从最顶层到自己这一层的完整ID信息。
举个栗子：
image
简略的表结构：

"CATY_ID"   VARCHAR2(50)  NOT NULL , 
"PARENT_ID" VARCHAR2(50)  NOT NULL , 
"FULL_ID"   VARCHAR2(200) NOT NULL ,

由于导入数据的时候full_id被我搞拐了而且分类下已经有很多数据了，所以现在需要用sql构建出每个分类的full_id

构建出的full_id

image

实现

原理就是用递归查询，然后用concat拼接，最后得到全路径ID

UPDATE CATY c
SET c.full_id = (WITH category_path(caty_id,parent_id,full_id) AS (
	  SELECT caty_id, parent_id, CONCAT('/',CATY_ID)  AS full_id 
	  FROM CATY c1 
	  WHERE c1.parent_id IS NULL
	  
	  UNION ALL
	  
	  SELECT c2.caty_id, c2.parent_id, CONCAT(CONCAT(cp.full_id, '/') , c2.CATY_ID) AS full_id
	  FROM CATY c2 
	  INNER JOIN category_path cp ON c2.parent_id = cp.caty_id
	)
	SELECT cp.full_id FROM category_path cp WHERE c.caty_id = cp.caty_id)

这个sql没有加where条件，会修改整个表的数据，用的时候注意一下！！！