获取无限级下级菜单
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