获取无限级下级菜单

	WITH tt(cid,parent_cid)
	AS(
		SELECT ic.cid,ic.parent_cid FROM ItemCat ic WHERE ic.cid=@CID
		UNION ALL
		SELECT ic.cid,ic.parent_cid FROM ItemCat ic JOIN tt t ON ic.parent_cid=t.cid
	)
	SELECT * FROM tt t ORDER BY t.parent_cid

 

--取其下的所有子级
WITH b AS (
SELECT id,CName,pid FROM dbo.ItemCata WHERE id=16
UNION ALL
SELECT a.id,a.CName,a.pid FROM dbo.ItemCata a ,b WHERE a.pid=b.id
) 
SELECT * FROM b

 

--取其上的所有父级
WITH b AS (
SELECT id,CName,pid FROM dbo.ItemCata WHERE id =91
UNION ALL
SELECT  a.id,a.CName,a.pid FROM dbo.ItemCata a ,b WHERE a.id=b.pid
) 
SELECT DISTINCT * FROM b