mysql子类是什么_MySQL 子类节点查询

以前做过类似的查询，是关于商品分类的，不过当时用的是MySQL自定义函数里面写的递归，实现比较简单。这里如果只用一句SQL查询，只能利用SQL变量做文章了。大致说下实现思路：

按parent_id从小到大遍历depts所有记录，不断拼接@all_path变量，遍历完之后，取最后一条记录,3,4,7,8,6,1,:,5,2,

很显然，上面每个根节点为0的都是分号分隔了，根据TG的id=1再次做一个字符串处理，得到1,3,4,7,8,6

最后联合users表查询即可

SELECT

u.*

FROM

depts AS d,

users AS u,

(SELECT

depts_node

FROM

(SELECT

@auto_id:=@auto_id + 1 AS auto_id,

@all_path:=IF(parent_id = 0, CONCAT(@all_path, ':,', id, ','), REPLACE(@all_path, CONCAT(',', parent_id, ','), CONCAT(',', id, ',', parent_id, ','))) AS depts_node

FROM

depts, (SELECT @auto_id:=0, @all_path:='') vv

ORDER BY parent_id ASC) AS all_depts

ORDER BY auto_id DESC

LIMIT 0 , 1) AS f

WHERE

d.dept_name = 'TG'

AND FIND_IN_SET(u.dept_id,

CONCAT(d.id,

SUBSTRING_INDEX(SUBSTRING_INDEX(f.depts_node, CONCAT(',', d.id, ','), 1),

':',

- 1)))