Zi 字媒體

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