业务需要根据一个子节点查询出根节点的信息。做无限分类经常用到。
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`) )
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
select * from table start with id=1 connect by id = prior p_id
select * from table start with p_id=4 connect by prior id = p_id
原文：https://www.cnblogs.com/ZJOE80/p/15167627.html