MYSQL 遞迴搜尋『部門階層』正/反搜尋

資料來源:

    https://cloud.tencent.com/developer/ask/50202
    https://codeday.me/bug/20170721/46103.html
    http://qincidong.github.io/blog/2015/01/31/mysql-endless-sort-query.html

資料庫結構+資料

CREATE TABLE IF NOT EXISTS `department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `unit` int(11) DEFAULT NULL,
  `descript` text,
  `state` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=30 ;

INSERT INTO `department` (`id`, `name`, `unit`, `descript`, `state`) VALUES
(1, '行政', 0, NULL, 1),
(2, '會計', 1, NULL, 1),
(3, '人事', 1, NULL, 1),
(4, '研發', 0, NULL, 1),
(5, '軟體', 4, NULL, 1),
(6, '硬體', 4, NULL, 1),
(-1, '未分類', 0, NULL, 1),
(27, 'SW01', 5, NULL, 1),
(28, 'SW02', 5, NULL, 1),
(29, 'SW01-01', 27, NULL, 1);

正向搜尋(研發下面的所有節點 id=4):[對於非常大的數據集，此解決方案可能會變慢，因為FIND_IN_SET操作不是在列表中查找數字的最理想方法]

SELECT  id,
        name,
        unit 
FROM    (SELECT * FROM department
         order by unit, id) department_sorted,
        (SELECT @pv := '4') initialisation
WHERE   find_in_set(unit, @pv) > 0
AND     @pv := concat(@pv, ',', id)

正向搜尋(研發下面的所有節點 id=4):[限定包含自己層數=4]

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM department AS t1
LEFT JOIN department AS t2 ON t2.unit = t1.id
LEFT JOIN department AS t3 ON t3.unit = t2.id
LEFT JOIN department AS t4 ON t4.unit = t3.id
WHERE t1.id = 4;

反向搜尋(SW01-01上面所有的父節點 id=29)

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