MySQL 子類節(jié)點查詢

mysql模擬遞歸查詢
查詢TG下的所有部門，加一個paths like ',0,1'就可以了。

SELECT id AS ID,pid AS 父ID ,levels AS 父到子之間級數(shù), 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

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

sql不好處理的，你這個是層層遞進的，這種循環(huán)處理要放在代碼里面做才好的

以前做過類似的查詢，是關(guān)于商品分類的，不過當時用的是MySQL自定義函數(shù)里面寫的遞歸，實現(xiàn)比較簡單。這里如果只用一句SQL查詢，只能利用SQL變量做文章了。大致說下實現(xiàn)思路：

• 按parent_id從小到大遍歷depts所有記錄，不斷拼接@all_path變量，遍歷完之后，取最后一條記錄,3,4,7,8,6,1,:,5,2,

• 很顯然，上面每個根節(jié)點為0的都是分號分隔了，根據(jù)TG的id=1再次做一個字符串處理，得到1,3,4,7,8,6

• 最后聯(lián)合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)))

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

--不知道我理解對沒有、