mysql根据子级查询父级， 根据父级查询子级（递归查询）

创建表

CREATE TABLE `menu` (
  `id` bigint(20) NOT NULL COMMENT 'ID',
  `name` varchar(50) NOT NULL DEFAULT '' COMMENT '菜单名称',
  `parent_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '上级菜单ID',
  PRIMARY KEY (`id`),
  KEY `idx_valid` (`parent_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='菜单表';

递归查询当前id和父id（根据子查询父）：

SELECT
@r AS menuId,
(SELECT @r := parent_id FROM menu WHERE id = menuId) AS parentId,
@l := @l + 1 AS lvl FROM (SELECT @r := 754384990610804736, @l := 0) vars,
menu h WHERE @r <> 0 AND parent_id > 0;

查询的结果：

还有一种方式（根据子查询父）：

原数据：

查询sql如下： 

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

查询结果：

直接根据子ID查询所有父级，并拼接成 / 拼接的字符串SQL如下：

SELECT GROUP_CONCAT(DISTINCT T2.name  ORDER BY T2.id ASC SEPARATOR '/') as  name
FROM ( 
    SELECT 
        @r AS _id, 
        (SELECT @r := parent_id FROM material_category WHERE id = _id) AS parent_id, 
        @l := @l + 1 AS lvl 
    FROM 
        (SELECT @r := 687720649089466368, @l := 0) vars, 
        material_category h 
    WHERE @r <> 0) T1 
JOIN material_category T2 
ON T1._id = T2.id 
ORDER BY T1.lvl DESC;

执行结果如下：

递归查询当前id和父id（根据父查询子）：

SELECT id, parent_id
FROM (
SELECT t1.id, t1.parent_id,   IF ( FIND_IN_SET(parent_id, @pids) > 0, @pids := CONCAT(@pids, ',', id), 0) AS ischild
FROM (SELECT id, parent_id
           FROM menu t 
           ORDER BY parent_id, id
           ) t1,
           (SELECT @pids := 747044428764930048) t2
) t3
WHERE ischild != 0 OR id = 747044428764930048;

查询结果：

根据父查询子（父子关系表在两个表中，比如id在表1，parentId在表2中可用以下sql查询）：

-- ① 通过sql查询将 id和parent_id放到一个表中
-- select su.*,(select parent_id from sys_dept where dept_id = su.dept_id) parent_id from sys_user su 

-- ② 将下面table_name换成以上sql即可
SELECT
	id 
FROM
	(
	SELECT
		t1.id,
	IF
		(
			FIND_IN_SET( t1.parent_id, @pids ) > 0,
			@pids := CONCAT( @pids, ',', t1.id ),
			0 
		) AS ischild 
	FROM
		( SELECT id, parent_id FROM table_name t ORDER BY parent_id, id ) t1,
		( SELECT @pids := '16' ) t2 
	) t3 
WHERE
	ischild != 0 
	OR id = '16'

替换后的查询结果：