递归查询，临时表的高级应用

WITH tempAS(--父项SELECT * FROM Ar_Area WHERE Ar_Parent = 1UNION ALL--递归结果集中的下级SELECT m.* FROM Ar_Area AS mINNER JOIN temp AS child ON m.Ar_Parent = child.Ar_Code)SELECT * FROM temp

实际应用：

只查一个父ID的所有子分类包括自己

WITH temp AS(--父项SELECT * FROM tg_ProductCategory WHERE CategoryKey = 'BaiGe'UNION ALL--递归结果集中的下级SELECT m.* FROM tg_ProductCategory AS mINNER JOIN temp AS child ON m.Parentid = child.ProductCategoryid)SELECT ProductCategoryId,ParentId,ProductCategoryName FROM temp

查询结果如图：

 

如果查某商品是否属于跟节点【特价商品】的就用

WITH temp AS(--父项SELECT * FROM tg_ProductCategory WHERE CategoryKey = 'BaiGe'UNION ALL--递归结果集中的下级SELECT m.* FROM tg_ProductCategory AS mINNER JOIN temp AS child ON m.Parentid = child.ProductCategoryid)select * from tg_GroupProduct where NewCategoryId in(SELECT ProductCategoryId FROM temp) and GroupProductId= 1232