/****** Script for SelectTopNRows command from SSMS ******/
--查询某个节点上的所有父节点 如果需要过滤在where后面跟过滤条件
WITH MyTest
AS (
--起始条件
SELECT id,
title,
parent
FROM tb_menu
WHERE id = 44 --列出子节点查询条件
UNION ALL --http://www.w3school.com.cn/sql/sql_union.asp
--递归条件
SELECT a.id,
a.title,
a.parent
FROM tb_menu a
INNER JOIN MyTest b --执行递归,这里就要理解下了
ON a.id = b.parent)
SELECT *
FROM MyTest;
--查询父节点下面的子节点
WITH cte_child (id, title, parent, level)
AS (
--起始条件
SELECT id,
title,
parent,
AS level
FROM tb_menu
WHERE id = 6 --列出父节点查询条件
UNION ALL
--递归条件
SELECT a.id,
a.title,
a.parent,
b.level + 1
FROM tb_menu a
INNER JOIN cte_child b
ON (a.parent = b.id))
SELECT *
FROM cte_child;