mysql 查找子级相同的_MYSQL 查找所有的父级或子级（多级）

语句的作用

如果用php程序查询的话，要用递归多次查询数据库，很慢，也增加了服务器的压力，后来发现用一句sql就能直接返回所有的父级或子级

用户表

t_id | parent_id

1 | 0

2 | 1

3 | 0

4 | 2

5 | 4

查找父级例子

sql语句 查找所有父级(包含本身)

SELECT T2.t_id

FROM (

SELECT

@r AS _id,

(SELECT @r := parent_id FROM t_user WHERE t_id = _id) AS parent_id,

@l := @l + 1 AS lvl FROM (SELECT @r := 5, @l := 0) vars,

t_user h WHERE @r <> 0 AND parent_id > 0

) T1 JOIN t_user T2 ON T1._id = T2.t_id ORDER BY T1.lvl DESC

筛选结果

|t_id|

|1 |

|2 |

|4 |

|5 |

查找子级例子

sql语句 查找所有子级(不包含本身)

SELECT t_id FROM

(

SELECT * FROM t_user where parent_id > 0 ORDER BY parent_id, t_id DESC

) realname_sorted,

(SELECT @pv :=1) initialisation

WHERE (FIND_IN_SET(parent_id,@pv)>0 And @pv := concat(@pv, ',', t_id))

筛选结果

|t_id|

|2 |

|4 |

|5 |

查找子级是同事写的，查找父级是自己写的，记下来方便以后用

根据一级节点查找对应的二级子节点数量

SELECT t_id, ( SELECT count(a.parent_id) FROM t_user a WHERE a.parent_id = b.t_id) count FROM t_user b WHERE b.parent_id = 0

筛选结果

|t_id|count

|1 | 1

|3 | 0

参考文章

http://blog.csdn.net/itchiang/article/details/9270245