MySQL 子类节点查询

Question

最近在面试看到一个很有趣的问题, 想征集一下大家有什么更好的思路来实现。。

• 有两张数据表: depts, users

其中第一张：

id	parent_id	type_id	dept_name
1	0	1	TG
2	0	1	SG
3	1	2	设计部
4	1	2	研发部
5	2	1	产品部
6	1	5	运维安全线
7	6	2	安全平台部
8	6	2	网络平台部

第二张:

id	dept_id	user_name
1	3	小明
2	3	小红
3	3	小张
4	4	小王
5	4	老张
6	4	小黑
7	5	大黄
8	7	大虾
9	7	零七
10	8	天空
11	8	玛丽

• 要求找出 表中 TG的所有员工。

我想到的最便捷的方式是。。添加字段。。path.
(例如 安全平台部 path：0-1-6-7)， 但是原题是上不允许这样的做的， 如果要Join 表的话，可能需要递归操作。。。所以想让大家看看有什么好的思路。。

Answer 1

以前做过类似的查询，是关于商品分类的，不过当时用的是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)))

Answer 2

mysql模拟递归查询
查询TG下的所有部门，加一个paths like ',0,1'就可以了。

SELECT id AS ID,pid AS 父ID ,levels AS 父到子之间级数, paths AS 父到子路径 FROM (
    SELECT id,pid,
        @le:= IF (pid = 0 ,0,
        IF( LOCATE( CONCAT('|',pid,':'),@pathlevel) > 0  ,
        SUBSTRING_INDEX( SUBSTRING_INDEX(@pathlevel,CONCAT('|',pid,':'),-1),'|',1) +1
        ,@le+1) ) levels
        , @pathlevel:= CONCAT(@pathlevel,'|',id,':', @le ,'|') pathlevel
        , @pathnodes:= IF( pid =0,',0',
        CONCAT_WS(',',
        IF( LOCATE( CONCAT('|',pid,':'),@pathall) > 0  ,
        SUBSTRING_INDEX( SUBSTRING_INDEX(@pathall,CONCAT('|',pid,':'),-1),'|',1)
        ,@pathnodes ) ,pid  ) )paths
        ,@pathall:=CONCAT(@pathall,'|',id,':', @pathnodes ,'|') pathall
    FROM  treenodes,
    (SELECT @le:=0,@pathlevel:='', @pathall:='',@pathnodes:='') vv
    ORDER BY  pid,id
) src
ORDER BY id

Answer 3

select user.*,dept.dept_name from user join dept on user.dept_id=dept.id where dept.dept_name='TG';

Answer 4

SELECT [users].Id,[users].user_name FROM [users] LEFT JOIN [depts] ON  [users].dept_id=[depts].Id WHERE [depts].dept_name='TG'

--不知道我理解对没有、

Answer 5

sql不好处理的，你这个是层层递进的，这种循环处理要放在代码里面做才好的