sql查询所有父级、所有子级

参考资料
https://www.cnblogs.com/rainydayfmb/p/8028868.html

存在数据表如下，要求给定分类id，查询所有父级或子级

CREATE TABLE `smdm_item_category` (
  `category_id` double DEFAULT NULL,
  `category_code` varchar(255) DEFAULT NULL,
  `category_name` varchar(255) DEFAULT NULL,
  `parent_category_id` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO `smdm_item_category`(`category_id`, `category_code`, `category_name`, `parent_category_id`) VALUES (1752, 'IP', '间接采购\r\n', 0);
INSERT INTO `smdm_item_category`(`category_id`, `category_code`, `category_name`, `parent_category_id`) VALUES (1753, 'IP-ITEM\r\n', '间接材料\r\n', 1752);
INSERT INTO `smdm_item_category`(`category_id`, `category_code`, `category_name`, `parent_category_id`) VALUES (1773, 'IP-ITEM-CYS\r\n', '印刷品\r\n', 1753);
INSERT INTO `smdm_item_category`(`category_id`, `category_code`, `category_name`, `parent_category_id`) VALUES (1909, 'IP-ITEM-CYS-YHSC', '用户手册\r\n', 1773);
INSERT INTO `smdm_item_category`(`category_id`, `category_code`, `category_name`, `parent_category_id`) VALUES (2230, 'MANUAL,OWNERS', '用户手册\r\n', 1909);

语法介绍：

1. 函数FIND_IN_SET(str,strlist) ： str 要查询的字符串，strlist 需查询的字段，参数以","分隔，形式如 (1,2,6,8,10,22)；该函数的作用是查询字段(strlist)中是否包含(str)的结果，返回结果为0 或 str在后边集合strlist中的位置，如 select find_in_set(‘d’,‘a,b,c’) 是 0

2. if(express1,express2,express3）条件语句，if语句类似三目运算符，当exprss1成立时，执行express2,否则执行express3;

3. := 在set、update、select时，起到赋值作用，SELECT @pids := 1773 即@pids赋值为1773

查询所有子级（含本级）

SELECT
	category_id ,category_code,category_name
FROM
	(
	SELECT
		t1.category_id,
		t1.category_code,
		t1.category_name,
	IF
		( find_in_set( parent_category_id, @pids ) > 0, @pids := concat( @pids, ',', category_id ), 0 ) AS ischild 
	FROM
		( SELECT category_id, category_code,category_name,parent_category_id FROM smdm_item_category t ORDER BY parent_category_id, category_id ) t1,
		( SELECT @pids := 1773 ) t2 
	) t3 
WHERE
	ischild != 0 or category_id = 1773 

查所有父级，含级别

-- 递归查询所有父级，@r后面输入要查询的id
-- T1.lvl 表示层级  @l := 0 初始为0
SELECT T2.category_id, T2.category_code ,T2.category_name,T1.lvl
FROM ( 
    SELECT 
        @r AS _id, 
        (SELECT @r := parent_category_id FROM smdm_item_category WHERE category_id = _id) AS parent_category_id,
				@l := @l + 1 AS lvl
    FROM 
        (SELECT @r := 2230,@l := 0 ) vars, 
        smdm_item_category h 
    ) T1 
JOIN smdm_item_category T2 
ON T1._id = T2.category_id

---

---

#创建表

DROP TABLE IF EXISTS `test`;

CREATE TABLE `test` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
  `parent_id` bigint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

#插入数据

insert into `test` (`id`, `name`, `parent_id`) values('1','A','0');
insert into `test` (`id`, `name`, `parent_id`) values('2','B','1');
insert into `test` (`id`, `name`, `parent_id`) values('3','C','1');
insert into `test` (`id`, `name`, `parent_id`) values('4','D','2');
insert into `test` (`id`, `name`, `parent_id`) values('5','E','4');
insert into `test` (`id`, `name`, `parent_id`) values('6','F','1');
insert into `test` (`id`, `name`, `parent_id`) values('7','G','1');

#执行sql

SELECT T2.id, T2.name 
FROM ( 
    SELECT 
        @r AS _id, 
        (SELECT @r := parent_id FROM test WHERE id = _id) AS parent_id, 
        @l := @l + 1 AS lvl 
    FROM 
        (SELECT @r := 5, @l := 0) vars, 
        test h 
    ) T1 
JOIN test T2 
ON T1._id = T2.id 
ORDER BY T1.lvl DESC