子栏目ID按层级查出父栏目(拆解篇)

本文学习于这篇文章
https://blog.csdn.net/Biexiansheng/article/details/81290352

表:

CREATE TABLE `catelog` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `parent_id` bigint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

INSERT INTO `catelog` VALUES (1, '军事', 0);
INSERT INTO `catelog` VALUES (2, '法律', 0);
INSERT INTO `catelog` VALUES (3, '军事子', 1);
INSERT INTO `catelog` VALUES (4, '法律子', 2);
INSERT INTO `catelog` VALUES (5, '军事滋滋', 3);
INSERT INTO `catelog` VALUES (6, '法律滋滋', 4);

语句:

 1 SELECT T2.*
 2 FROM ( 
 3         SELECT 
 4                 @r AS _id, 
 5                 (SELECT @r := parent_id FROM catelog WHERE id = _id) AS parent_id, 
 6                 @l := @l + 1 AS lvl 
 7         FROM 
 8                 (SELECT @r := 6, @l := 0) vars,
 9                 catelog h 
10         WHERE @r <> 0) T1 
11 JOIN catelog T2 
12 ON T1._id = T2.id 
13 ORDER BY T1.lvl DESC 

我们就是来拆解一下
@r := 6 //定义一个变量 要查询的子栏目ID

@l := 0 //层级 最底层为0

(SELECT @r := 6, @l := 0) vars //这就是定义变量的规定写法

(SELECT @r := parent_id FROM catelog WHERE id = _id) AS parent_id //最核心的就是这一句 查到 id为6的 就把id覆盖为parent_id

        SELECT 
                  @r AS _id, 
                 (SELECT @r := parent_id FROM catelog WHERE id = _id) AS parent_id, 
                 @l := @l + 1 AS lvl 
         FROM 
                 (SELECT @r := 6, @l := 0) vars,
                 catelog h 
         WHERE @r <> 0) T1 

上面看懂了 这也就不用解释了

最后JOIN 也不解释了