如何查询子类别数据

[JasonZhou]

有两个表
ArticleCategory[文章类别表(无限级)]
字段：CategoryId,ParentId,CategoryName


Article[文章内容表]
字段：ArticleId,CategoryId,Title,Content

我现在想要查询Article表中一个类别、及类别下所有子类别有文章

这个存储过程要怎么写？

[东那个升]

create proc sp_name (@CategoryId  int)

as

with cte as(
select a.CategoryName,b.*,0 as levl from ArticleCategory a, Article b
where a.CategoryId=b.@CategoryId
union all
select a.CategoryName,b.*, levl+1 from  ArticleCategory a, Article b, cte c
where a.CategoryId=b.CategoryId and a.ParentId=c.CategoryId
)

select * from cte

[htl258_Tony]

with t as
(
  select a.CategoryName,b.Title,b.Content
  from ArticleCategory a 
    join Article b on a.CategoryId=b.CategoryId
  where a.CategoryName='你提供的类别名'
  union all 
  select a.CategoryName,b.Title,b.Content 
  from ArticleCategory a 
    join Article b on a.CategoryId=b.CategoryId
    join t c on a.ParentId=c.CategoryId
)
select * from t