CREATE TABLE test(id int,parentid int,name varchar(20)) insert into test select 1,0,'aa' union all select 2,1,'bb' union all select 3,2,'cc' union all select 4,3,'dd' --建函数 CREATE Function F_Tree(@id varchar(10)) returns @t table(id int,parentid int,name varchar(10),level int) begin declare @i int set @i=1 insert @t select *,@i from test where parentid=@id while @@rowcount<>0 begin set @i=@i+1 insert @t select a.*,@i from test a,@t b where b.id=a.parentid and b.level=@i-1 end return endselect * from dbo.F_Tree(1)/* id parentid name level ----------- ----------- ---------- ----------- 2 1 bb 1 3 2 cc 2 4 3 dd 3(所影响的行数为 3 行)*/
CREATE TABLE test(id int,parentid int,name varchar(20)) insert into test select 1,0,'aa' union all select 2,1,'bb' union all select 3,2,'cc' union all select 4,3,'dd' union all select 5,0,'AA' union all select 6,1,'BB' select '|-'+replicate('-',parentid)+ name from test order by parentid
--定义CTE(公共表表达式) --SQL server 2005 可以用CTE 实现数据库递归。 (int,int,varchar(10),int DECLARE @id AS INT SET @id =1; WITH ACTE(id,parentid,name,level) AS ( SELECT id,parentid,name,level,0 FROM A WHERE parentid=@id UNION ALL SELECT ta.id,ta.parentid,ta.name,TB.level+1 FROM A TA INNER JOIN ACTE TB ON TA.PARENTID=TB.ID ) SELECT * FROM ACTE
to zlp321002(龙卷风2006):能不能用一句sql语句得出呢
sql server 2005 就一句SQL 嘛!sql server 2000 就得写个函数了。
不知道Lz是不是这个意思 select *,parentname=(select top 1 name from test Id=A.parentID) where from test A
支持cte (int,int,varchar(10),int DECLARE @id AS INT SET @id =1; --注意这个分号 WITH tree(id,parentid,name,level) AS ( SELECT id,parentid,name,level,0 FROM A WHERE parentid=@id UNION ALL SELECT a.id,a.parentid,a.name,t.level+1 FROM A INNER JOIN tree t ON A.PARENTID=t.ID --t.level<2 按控弟归次数 ) SELECT * FROM ACTE
insert into test select 1,0,'aa'
union all select 2,1,'bb'
union all select 3,2,'cc'
union all select 4,3,'dd'
--建函数
CREATE Function F_Tree(@id varchar(10))
returns @t table(id int,parentid int,name varchar(10),level int)
begin
declare @i int
set @i=1
insert @t select *,@i from test where parentid=@id
while @@rowcount<>0
begin
set @i=@i+1
insert @t select a.*,@i from test a,@t b
where b.id=a.parentid and b.level=@i-1
end
return
endselect * from dbo.F_Tree(1)/*
id parentid name level
----------- ----------- ---------- -----------
2 1 bb 1
3 2 cc 2
4 3 dd 3(所影响的行数为 3 行)*/
insert into test select 1,0,'aa'
union all select 2,1,'bb'
union all select 3,2,'cc'
union all select 4,3,'dd'
union all select 5,0,'AA'
union all select 6,1,'BB'
select '|-'+replicate('-',parentid)+ name
from test
order by parentid
--SQL server 2005 可以用CTE 实现数据库递归。
(int,int,varchar(10),int
DECLARE @id AS INT
SET @id =1;
WITH ACTE(id,parentid,name,level)
AS
(
SELECT id,parentid,name,level,0
FROM A WHERE parentid=@id
UNION ALL
SELECT ta.id,ta.parentid,ta.name,TB.level+1
FROM A TA INNER JOIN ACTE TB
ON TA.PARENTID=TB.ID
)
SELECT * FROM ACTE
select *,parentname=(select top 1 name from test Id=A.parentID) where from test A
(int,int,varchar(10),int
DECLARE @id AS INT
SET @id =1; --注意这个分号
WITH tree(id,parentid,name,level)
AS
(
SELECT id,parentid,name,level,0
FROM A WHERE parentid=@id
UNION ALL
SELECT a.id,a.parentid,a.name,t.level+1
FROM A INNER JOIN tree t
ON A.PARENTID=t.ID --t.level<2 按控弟归次数
)
SELECT * FROM ACTE