-- 控制树的查询深度
ALTER VIEW [dbo].[V_GoodsCategoryTree]
AS
WITH GoodsCategoryTree
AS
(
select GCId, GCCode, GCParentCode, GCParentCode AS TopParentCode,
(select gc.GCName from GoodsCategory as gc where gc.GCCode = GoodsCategory.GCParentCode) AS TopParentName,
GCCode AS SubClassCode, 1 AS depth from GoodsCategory
where GCParentCode IN(select GCCode from GoodsCategory where GCParentCode = '' )
union all
select r.GCId,r.GCCode,r.GCParentCode,TopParentCode,TopParentName,SubClassCode, depth + 1 as depth from GoodsCategory as r
join GoodsCategoryTree as rp on r.GCParentCode = rp.GCCode
)
select * from GoodsCategoryTree
GO