表名:tb_menu
内容如下:
查询ID为3的所有无限层下级会员
WITH T
AS(
SELECT * FROM tb_menu WHERE id=3
UNION ALL
SELECT a.* FROM tb_menu a INNER JOIN T ON a.parent=T.id
)
SELECT * FROM T
结果图如下:
查询Id为3所有的上级
WITH T
AS(
SELECT * FROM tb_menu WHERE id=3
UNION ALL
SELECT a.*
FROM tb_menu a INNER JOIN T ON a.id=T.parent
)
SELECT * FROM T
结果如下图: