Sql 无限级（向上、向下）查询

无限向上查询

WITH T
AS( 
    SELECT uid,upid,uloginname FROM tb_User WHERE uId=5
    UNION ALL 
    SELECT U.uid,U.upid,U.uloginname   
    FROM tb_User U INNER JOIN T ON U.uid=T.upid  --and u.ulev>0 递归条件
) 
SELECT * FROM T --where t.uid!=5 --结果条件

无限向下查询

WITH T
AS( 
    SELECT uid,upid,uloginname FROM tb_User WHERE uId=5
    UNION ALL 
    SELECT U.uid,U.upid,U.uloginname   
    FROM tb_User U INNER JOIN T ON u.upid=t.uid  --and u.ulev>0 递归条件
) 
SELECT * FROM T --where t.uid!=5 --结果条件

 

区别在于：u.uid=t.upid ( 向上查 ) ，u.upid=t.uid ( 向下查 )