34,590
社区成员
发帖
与我相关
我的任务
分享
create table Article_Category(CatgoryID int,CategoryName int,CategoryParentID varchar(50))
insert into Article_Category select 1,0,'程序设计'
insert into Article_Category select 2,1,'C#'
insert into Article_Category select 3,1,'Delphi'
insert into Article_Category select 4,2,'C#语法介绍'
insert into Article_Category select 5,2,'C#功能介绍'
insert into Article_Category select 6,0,'新闻'
create table Article(ArticleID int,CatgoryID int,ArticleTitle varchar(50),ArticleCreateDate datetime)
insert into Article select 1,4,'C#语法介绍二','2008-1-5'
insert into Article select 2,5,'C#功能介绍','2008-1-4'
insert into Article select 3,6,'新闻标题一','2008-1-3'
insert into Article select 4,4,'C#语法介绍一','2008-1-2'
create function get(@CatgoryID int)
returns @t table(CatgoryID int,CategoryName int,CategoryParentID VARCHAR(50),Level int)
as
begin
declare @i int
set @i=1
insert into @t select CatgoryID,CategoryName,CategoryParentID,@i from Article_Category where CategoryName=1
while @@rowcount<>0
begin
set @i=@i + 1
insert into @t select a.CatgoryID,a.CategoryName,a.CategoryParentID,@i from Article_Category a,@t b
where a.CategoryName=b.CatgoryID and b.Level=@i-1
end
return
end
select articleid,a.CatgoryID,articletitle,ArticleCreateDate from (select * from dbo.get(1)) a,Article b
where a.CatgoryID=b.CatgoryID