Mysql 递归查询子节点

Mysql递归查询

#查询父编码及以下所有子节点
SELECT id FROM (
SELECT t1.id,t1.parent_id,IF (find_in_set(t1.id,@pids)> 0,@pids,IF (find_in_set(t1.parent_id,@pids)> 0,@pids :=CONCAT_WS(',',@pids,id),'0')) AS ischild,@pids FROM (
SELECT t.id,t.parent_id FROM hr_organization t WHERE t.STATUS=1 ORDER BY parent_id,id) t1,(
SELECT @pids :='YZC0035') t2) t3 WHERE ischild !='0';
#查询子节点
SELECT id FROM (
SELECT t1.id,t1.parent_id, IF (find_in_set(t1.parent_id,@pids)> 0,@pids :=CONCAT_WS(',',@pids,id),'0') AS ischild,@pids FROM (
SELECT t.id,t.parent_id FROM hr_organization t WHERE t.STATUS=1 ORDER BY parent_id,id) t1,(
SELECT @pids :='YZC0035') t2) t3 WHERE ischild !='0';

1. @pids :=‘YZC0035’ 定义变量，赋值要查询的父编码
2. @pids 取值
3. find_in_set(‘查询的字段’,‘以逗号分隔字符串’) select find_in_set(‘2’,‘1,2’);返回2 不存在返回0
4. if（express1,express2,express3）条件语句，if语句类似三目运算符，当exprss1成立时，执行express2,否则执行express3
5. CONCAT_WS 合并字段定义以分隔符