SQL 有父标识的 递归查询

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

WITH temp
AS
(
--父项
SELECT * FROM Ar_Area WHERE Ar_Parent = 1
UNION ALL
--递归结果集中的下级
SELECT m.* FROM Ar_Area AS m
INNER 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 m
INNER 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 m
INNER JOIN temp AS child ON m.Parentid = child.ProductCategoryid
)
select * from tg_GroupProduct where NewCategoryId in(
SELECT ProductCategoryId FROM temp) and GroupProductId= 1232