关于MySQL递归查询，从子类ID查询所有父类

一、业务场景

业务需要根据一个子节点查询出根节点的信息。做无限分类经常用到。

 

二、表结构

CREATE TABLE `t_busi_system` (
  `ID` varchar(64) NOT NULL COMMENT '标识',
  `PARENT_ID` varchar(64) DEFAULT NULL COMMENT '父id',
  `CREATE_DATE` varchar(64) DEFAULT NULL COMMENT '创建时间',
  `CREATE_USER_ID` varchar(64) DEFAULT NULL COMMENT '创建人id',
  `CREATE_USER_NAME` varchar(64) DEFAULT NULL COMMENT '创建人姓名',
  `LAST_UPDATE_DATE` varchar(64) DEFAULT NULL COMMENT '最后更新时间',
  `LAST_UPDATE_USER_ID` varchar(64) DEFAULT NULL COMMENT '最后更新人id',
  `LAST_UPDATE_USER_NAME` varchar(64) DEFAULT NULL COMMENT '最后更新人姓名',
  `SYSTEM_NAME` varchar(255) DEFAULT NULL COMMENT '系统名称'
  PRIMARY KEY (`ID`)
)

 

三、SQL语句

SELECT T2.*
FROM ( 
    SELECT 
        @r AS _id, 
        (SELECT @r := parent_id FROM T_BUSI_SYSTEM WHERE id = _id) AS parent_id, @l := @l + 1 AS lvl 
    FROM 
        (SELECT @r := '065efb0ee42e4c77a401bcf0f3eccf20', @l := 0) vars, T_BUSI_SYSTEM h WHERE @r <> 0) T1 
JOIN T_BUSI_SYSTEM T2 ON T1._id = T2.id 
ORDER BY T1.lvl DESC

 

四、oracle connect by实现

1、由叶子节点查根节点

select * from table start with id=1 connect by id = prior p_id

 

2、由父节点查叶子节点

select * from table start with p_id=4 connect by prior id = p_id