MySQL — 递归查询

MySQL — 递归查询

根据传入id查询所有父节点的id：

 SELECT T2.id, T2.name, T2.pid
 FROM (
     SELECT
         @r AS _id,
         (SELECT @r := pid FROM ${tableName} WHERE id = _id) AS pid,
         @l := @l + 1 AS l
     FROM
         (SELECT @r := #{id}, @l := 0) vars,
         ${tableName} h
     WHERE @r != 0) T1
 JOIN ${tableName} T2
 ON T1._id = T2.id AND T2.del_flag = '0'
 ORDER BY T1.l;

参考资料

• http://www.suoniao.com/article/5e4cc0a60bc99f7b351c478b