2、运行sql语句
SELECT T2.user_id, T2.user_name
FROM (
SELECT
@r AS _id,
(SELECT @r := parent_id FROM tb_user WHERE user_id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := 17, @l := 0) vars,
tb_user h
WHERE @r <> 0) T1
JOIN tb_user T2
ON T1._id = T2.user_id
ORDER BY T1.lvl DESC
得到结果为：
其中
@r := 17 表示 user_id为17的用户
假设有树形表，表结构和数据如下：
CREATE TABLE tree(
id
int, name varchar(10), p
id
int);
insert into tree values(0,‘中国’,null);
insert into tree values(1,‘四川’,0);
insert into tree values(2,‘贵州’,0);
insert into tree values(3,‘云南’,0);
insert into tree values(4,‘重庆’,0);
insert
CREATE TABLE `tree_table` (
`
id
` int(11) NOT NULL AUTO_INCREMENT,
`parent_
id
` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`
id
`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 插入记录
INSERT INTO `tree_table`(`
id
`, `pa
图中 dic_
id
为
子
健 parent_dic_
id
为
父
建 即 dic_ic = parent_dic_
id
现在要查出
父
id
为1 的所有
子
集
sql
对应为 ：
下面展示一些 内联代码片。
select
* from T_SYS_DIC
start with dic_
id
= '1000076' CONNECT BY PRIOR dic_
id
= parent_dic_
id
对应结果：可以看出所有数据均为
父
id
为 1000
SELECT
@r AS _
id
,
(
SELECT
@r := parent_
id
FROM patrol_store_classify WHERE
id
= _
id
) AS parent_
id
,
@l := @l + 1 AS lvl
(
SELECT
@r := 11, @l := 0) vars,
patrol_store_clas...
