CREATE TABLE T_TABLE(
id int IDENTITY(1,1) NOT NULL,
upid int NULL,
title [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_T_TABLE] PRIMARY KEY CLUSTERED
(
id ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]INSERT INTO T_TABLE (upid,title) values(0,'1TITLE')
INSERT INTO T_TABLE (upid,title) values(0,'2TITLE')
INSERT INTO T_TABLE (upid,title) values(0,'3TITLE')
INSERT INTO T_TABLE (upid,title) values(2,'4TITLE')
INSERT INTO T_TABLE (upid,title) values(2,'5TITLE')
INSERT INTO T_TABLE (upid,title) values(4,'6TITLE')
INSERT INTO T_TABLE (upid,title) values(3,'7TITLE')
INSERT INTO T_TABLE (upid,title) values(3,'8TITLE')
INSERT INTO T_TABLE (upid,title) values(7,'9TITLE')
INSERT INTO T_TABLE (upid,title) values(8,'10TITLE')
INSERT INTO T_TABLE (upid,title) values(9,'11TITLE')
INSERT INTO T_TABLE (upid,title) values(7,'12TITLE')
INSERT INTO T_TABLE (upid,title) values(12,'13TITLE')传入 id=3 lvl=1
返回表
id upid title lvl
3 0 3TITLE 1
7 3 7TITLE 2
9 7 9TITLE 3
11 9 11TITLE 4
12 7 12TITLE 3
13 12 13TITLE 4
8 3 8TITLE 2
这个函数怎么写呢?create function T_getChild(@ID INT,@Lvl INT)
returns @t table(ID INT,UPID INT,TITLE VARCHAR(50),Lvl INT)
as
begin
IF exists(SELECT * FROM T_TABLE WHERE upid=@ID)
begin
--循环递归部分怎么写啊
end
else
begin
insert into @t select ID,UPID,TITLE,@Lvl from T_TABLE where ID = @ID
end
end
--测试
select * from T_getChild(3,1)
id int IDENTITY(1,1) NOT NULL,
upid int NULL,
title [varchar](50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]INSERT INTO T_TABLE (upid,title) values(0,'1TITLE')
INSERT INTO T_TABLE (upid,title) values(0,'2TITLE')
INSERT INTO T_TABLE (upid,title) values(0,'3TITLE')
INSERT INTO T_TABLE (upid,title) values(2,'4TITLE')
INSERT INTO T_TABLE (upid,title) values(2,'5TITLE')
INSERT INTO T_TABLE (upid,title) values(4,'6TITLE')
INSERT INTO T_TABLE (upid,title) values(3,'7TITLE')
INSERT INTO T_TABLE (upid,title) values(3,'8TITLE')
INSERT INTO T_TABLE (upid,title) values(7,'9TITLE')
INSERT INTO T_TABLE (upid,title) values(8,'10TITLE')
INSERT INTO T_TABLE (upid,title) values(9,'11TITLE')
INSERT INTO T_TABLE (upid,title) values(7,'12TITLE')
INSERT INTO T_TABLE (upid,title) values(12,'13TITLE')
gocreate function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),UPID VARCHAR(10),TITLE VARCHAR(50),Level INT)
as
begin
declare @i int
set @i = 1
insert into @t select ID,UPID,title,@i from T_TABLE where UPID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.UPID,a.title,@i
from
T_TABLE a,@t b
where
a.UPID=b.ID and b.Level = @i-1
end
insert into @t select ID,UPID,title,0 from T_TABLE where ID = @ID
return
end
goselect * from f_getChild(3)
drop table t_table
drop function f_getchild
id int IDENTITY(1,1) NOT NULL,
upid int NULL,
title [varchar](50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]INSERT INTO T_TABLE (upid,title) values(0,'1TITLE')
INSERT INTO T_TABLE (upid,title) values(0,'2TITLE')
INSERT INTO T_TABLE (upid,title) values(0,'3TITLE')
INSERT INTO T_TABLE (upid,title) values(2,'4TITLE')
INSERT INTO T_TABLE (upid,title) values(2,'5TITLE')
INSERT INTO T_TABLE (upid,title) values(4,'6TITLE')
INSERT INTO T_TABLE (upid,title) values(3,'7TITLE')
INSERT INTO T_TABLE (upid,title) values(3,'8TITLE')
INSERT INTO T_TABLE (upid,title) values(7,'9TITLE')
INSERT INTO T_TABLE (upid,title) values(8,'10TITLE')
INSERT INTO T_TABLE (upid,title) values(9,'11TITLE')
INSERT INTO T_TABLE (upid,title) values(7,'12TITLE')
INSERT INTO T_TABLE (upid,title) values(12,'13TITLE')
gocreate function f_getChild(@ID VARCHAR(10),@lev int)
returns @t table(ID VARCHAR(10),UPID VARCHAR(10),TITLE VARCHAR(50),Level INT)
as
begin
declare @i int
set @i = 1
insert into @t select ID,UPID,title,@i from T_TABLE where UPID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.UPID,a.title,@i
from
T_TABLE a,@t b
where
a.UPID=b.ID and b.Level = @i-1
end
insert into @t select ID,UPID,title,@lev from T_TABLE where ID = @ID
return
end
goselect * from f_getChild(3,1)/*
ID UPID TITLE Level
---------- ---------- -------------------------------------------------- -----------
7 3 7TITLE 1
8 3 8TITLE 1
9 7 9TITLE 2
12 7 12TITLE 2
10 8 10TITLE 2
11 9 11TITLE 3
13 12 13TITLE 3
3 0 3TITLE 1*/
drop table t_table
drop function f_getchild
id int IDENTITY(1,1) NOT NULL,
upid int NULL,
title [varchar](50) COLLATE Chinese_PRC_CI_AS NULL
)INSERT INTO T_TABLE (upid,title) values(0,'1TITLE')
INSERT INTO T_TABLE (upid,title) values(0,'2TITLE')
INSERT INTO T_TABLE (upid,title) values(0,'3TITLE')
INSERT INTO T_TABLE (upid,title) values(2,'4TITLE')
INSERT INTO T_TABLE (upid,title) values(2,'5TITLE')
INSERT INTO T_TABLE (upid,title) values(4,'6TITLE')
INSERT INTO T_TABLE (upid,title) values(3,'7TITLE')
INSERT INTO T_TABLE (upid,title) values(3,'8TITLE')
INSERT INTO T_TABLE (upid,title) values(7,'9TITLE')
INSERT INTO T_TABLE (upid,title) values(8,'10TITLE')
INSERT INTO T_TABLE (upid,title) values(9,'11TITLE')
INSERT INTO T_TABLE (upid,title) values(7,'12TITLE')
INSERT INTO T_TABLE (upid,title) values(12,'13TITLE')create function T_getChild(@ID INT,@Lvl INT)
returns @t table(ID INT,UPID INT,TITLE VARCHAR(50),Lvl INT)
as
begin
declare @t1 table(ID INT,UPID INT,TITLE VARCHAR(50),Lvl INT,T_lvl varchar(100))
declare @T_Lvl int
set @T_Lvl=1
insert @t1 select ID,UPID,TITLE,@T_Lvl, cast(ID as varchar) + cast(UPID as varchar) from T_TABLE where id=@ID
while @@rowcount>0
begin
set @T_Lvl=@T_Lvl+1
insert @t1 select TA.ID,TA.UPID,TA.TITLE,@T_Lvl,T.T_lvl +cast(TA.ID as varchar) from T_TABLE TA,(select * from @t1 where Lvl=@T_Lvl-1) T where T.id=TA.UPID
end
insert @t select ID,UPID,TITLE,Lvl from @t1 where Lvl>=1 order by T_lvl
return
endselect * from dbo.T_getChild(3,1)
for
select [id],title from T_TABLE WHERE upid=@ID
declare @tid int,@title varchar(50)
open cur_t
fetch next from cur_t
into @tid,@title
while @@fetch_status=0
begin
insert into @t([id],upid,title,lvl) values(@tid,@ID,@title,@Lvl)
T_getChild(@tid ,@Lvl+1)
fetch next from cur_t
into @tid,@title
end
close cur_t
deallocate cur_t
CREATE FUNCTION [dbo].[getTree] (@id int)
RETURNS @t table(id int,upid int,title varchar(50),TLevel int) AS
BEGIN
declare @TLevel int
declare @RowCount int
set @TLevel=0
insert into @t select id,upid,title,@TLevel from T_TABLE where id=@id
set @rowCount=@@RowCount
while @rowCount>0
begin
insert into @t
select a.id,a.upid,a.title,@TLevel+1 from T_TABLE a join @t b on b.id=a.upid where b.TLevel=@TLevel
set @RowCount=@@rowcount
set @TLevel=@TLevel+1
end
return
END
用法:
select * from dbo.getTree(1)
返回的结果
id upid Title TLevel
1 0 1TITLE 0
9 1 9TITLE 1
10 1 10TITLE 1
11 9 11TITLE 2
12 11 12TITLE 3
13 12 13TITLE 4
returns @t table(ID INT,UPID INT,TITLE VARCHAR(50),Lvl INT)
as
begin
IF exists(SELECT * FROM T_TABLE WHERE upid=@ID)
begin
declare cur_t cursor
for
select [id],title from T_TABLE WHERE upid=@ID
declare @tid int,@title varchar(50)
open cur_t
fetch next from cur_t
into @tid,@title
while @@fetch_status=0
begin
insert into @t([id],upid,title,lvl) values(@tid,@ID,@title,@Lvl)
set @Lvl=@Lvl+1
insert into @t select * from sde.f_getChild(@tid ,@Lvl) a
fetch next from cur_t
into @tid,@title
end
close cur_t
deallocate cur_t
return
end
else
begin
insert into @t select ID,UPID,TITLE,@Lvl from T_TABLE where ID = @ID
return
end
return
end
create function myf_getChild(@ID INT,@Lvl INT)
returns @t table(ID INT,UPID INT,TITLE VARCHAR(50),Lvl INT)
as
begin
insert into @t select ID,UPID,TITLE,@Lvl from T_TABLE where ID = @ID
IF exists(SELECT * FROM T_TABLE WHERE upid=@ID)
begin
declare cur_t cursor
for
select id,title from T_TABLE WHERE upid=@ID
declare @tid int,@title varchar(50)
open cur_t
fetch next from cur_t
into @tid,@title
while @@fetch_status=0
begin
insert into @t select * from myf_getChild(@tid ,@Lvl+1)
fetch next from cur_t
into @tid,@title
end
close cur_t
deallocate cur_t
end
return
end
returns @t table(IDn INT IDENTITY(1,1) NOT NULL,ID INT,UPID INT,TITLE VARCHAR(50),Lvl INT)
as
begin
declare @t1 table(ID INT,UPID INT,TITLE VARCHAR(50),Lvl INT,T_lvl varchar(100))
declare @T_Lvl int
set @T_Lvl=1
insert @t1 select ID,UPID,TITLE,@T_Lvl, cast(ID as varchar) + cast(UPID as varchar) from T_TABLE where id=@ID
while @@rowcount>0
begin
set @T_Lvl=@T_Lvl+1
insert @t1 select TA.ID,TA.UPID,TA.TITLE,@T_Lvl,T.T_lvl +cast(TA.ID as varchar) from T_TABLE TA,(select * from @t1 where Lvl=@T_Lvl-1) T where T.id=TA.UPID
end
insert @t select ID,UPID,TITLE,Lvl from @t1 where Lvl>=1 order by T_lvl
return
endselect a.* FROM T_TABLE a,(select * from my_getChild(3,1)) b WHERE a.id=b.id order by b.idn
CREATE FUNCTION getTree (@id int)
RETURNS @t table(id int,upid int,title varchar(50),TLevel int,LevelSort varchar(1000) PRIMARY KEY (LevelSort)) AS
begin
declare @TLevel int
set @TLevel=0
insert into @t select id,upid,title,@TLevel,str(ID,10) from T_TABLE where id=@id
while @@rowCount>0
begin
set @TLevel=@TLevel+1
insert into @t select a.id,a.upid,a.title,@TLevel,b.LevelSort+str(a.id,10) from T_TABLE a join @t b on b.id=a.upid where b.TLevel=@TLevel-1
end
return
end
将ID变为10个字符的字符串,这样在排序时出现问题的几率大大减小