根据某一个id查出所有父级、祖级、包括当前节点

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

根据某一个父级id查出所有子级、孙级、包括当前节点

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
            id, name, parent_id
        FROM
            (SELECT
             t1.id,
             IF(FIND_IN_SET(parent_id, @parentId) > 0, @parentId:=CONCAT(@parentId, ',', id), 0) AS ischild,
             name,parent_id
             FROM
             (SELECT
              id, parent_id, name
              FROM
              sys_city t
              ORDER BY parent_id , id, name) t1, (SELECT @parentId:= #{parentId}) t2) t3
        WHERE
            ischild != 0
						
union select id, name, parent_id from sys_city where id = #{parentId};