Mysql 查询所有的上级，下级

1.  
   -- 查询所有下级
2.  
   SELECT t3.id, t3.parentId pid, t3.tname name FROM (
3.  
   SELECT t1.*, IF(find_in_set( parentId, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) isChild
4.  
   FROM ( SELECT id, parentId, tname FROM TableName t ORDER BY parentId, id ) t1, ( SELECT @pids := 2 ) t2 ) t3
5.  
   WHERE
6.  
   isChild != 0
7.  
    

 说明tableName为表名，@pids:=2 代表是要查询的父id

1.  
   -- 查询所有上级
2.  
   SELECT t2.id, t2.parentId, t2.tname
3.  
   FROM (
4.  
   SELECT @id as _id,
5.  
   (SELECT @id := parentId FROM TableName WHERE id = _id) p
6.  
   FROM
7.  
   (SELECT @id := 4476) v,
8.  
   TableName
9.  
   WHERE @id <> 0) t1
10.  
    JOIN TableName t2 ON t1._id = t2.id

说明TableName 为表名，@id:=4476 代表要查询的子id

这篇好文章是转载于：学新通技术网