一个老问题，树形数据的查询
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Pro]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_Pro]
GO
CREATE TABLE [dbo].[T_Pro] (
[Pid] [int] IDENTITY (1, 1) NOT NULL ,
[DE_name] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[father_code] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
要求：用SQL语句来求出当前结点及其子结点的ID及数据，如果子结点还有子结点，也要显示出来！
[解决办法]
SQL2000 递归 收藏
drop table ta
create table ta(orderid int,parentid int,title nvarchar(100))
insert into ta
select 1 , 0 , N'新闻' union all
select 2 , 0 , N'人才' union all
select 3 , 1 , N'国内新闻' union all
select 4 , 3 , N'体育' union all
select 5 , 4 , N'蓝球' union all
select 6 , 1 , N'国际新闻' union all
select 7 , 2 , N'高级人才'
drop function f_id
create function f_id(@parentid nvarchar(10))
returns @re table(orderid int,parentid int,title nvarchar(20),level int)
as
begin
declare @l int
set @l=0
insert @re select orderid, parentid ,title,@l from ta
where parentid=@parentid
while @@rowcount>0
begin
set @l=@l+1
insert into @re select
a.orderid,a.parentid,a.title,@l
from ta a,@re b
where a.parentid=b.orderid and b.level=@l-1
end
return
end
go
select * from dbo.f_id(0)
