DEMOtb_city表结构如下 id name parentid 1 湖北 0 2 湖南 0 3 武汉 1 4 仙桃 1 5 长沙 2 6 蔡甸 3 1. create function c_tree(@initid int)/*定义函数c_tree,输入参数为初始节点id*/ 2. returns @t table(id int,name varchar(100),parentid int,lev int)/*定义表t用来存放取出的数据*/ 3. begin 4. declare @i int/*标志递归级别*/ 5. set @i=1 6. insert @t select id,name,parentid,@i from tb_city where id=@initid 7. while @@rowcount<>0 8. begin 9. set @i=@i+1 10. insert @t select a.id,a.name,a.parentid,@i from tb_city as a,@t as b 11. where b.id=a.parentid and b.lev=@i-1 12. end 13. return 14. end 你的create function c_tree(@initid int)/*定义函数c_tree,输入参数为初始节点id*/ returns @t table(id int,name varchar(100),parentid int,lev int)/*定义表t用来存放取出的数据*/ begin declare @i int/*标志递归级别*/ set @i=1 insert @t select id,name,pid ,@i from tb where id=@initid while @@rowcount<>0 begin set @i=@i+1 insert @t select a.id,a.name,a.pid ,@i from tb as a,@t as b where b.id=a.pid and b.lev=@i-1 end return end
id pid name 1 0 a 2 0 b 3 1 c 4 3 d 5 4 e要求 查出来的结果a c d e 真的没看懂吗
4楼的行,ORACLE里一句就能搞定.
Select name From TableName Order By id Desc
那里有相关的教程。 我见网上的都是教SQL 语句的。
declare @table table (A int,B int) insert into @table select 1,0 union all select 2,1 union all select 3,1 union all select 4,2 union all select 5,3 union all select 6,4 union all select 7,5; WITH Maco AS ( SELECT A , B FROM @table WHERE A = 1 UNION ALL SELECT a.A , a.B FROM @table AS a , Maco AS b WHERE a.B = b.A ) SELECT * FROM Maco
DECLARE @Table TABLE(Id int,Pid int,Name char)INSERT INTO @Table SELECT 1,0,'a' UNION ALL SELECT 2,0,'b' UNION ALL SELECT 3,1,'c' UNION ALL SELECT 4,3,'d' UNION ALL SELECT 5,4,'e' ;WITH List AS( SELECT * FROM @Table T WHERE T.Name='a' UNION ALL SELECT T2.* FROM List T , @Table T2 WHERE T.Id=T2.Pid )SELECT L.* FROM List L/* Id Pid Name ----------- ----------- ---- 1 0 a 3 1 c 4 3 d 5 4 e(4 行受影响) */
是oracle吗? select * from tablename start with name='e' connect by prior pid=id
其实你这少一个字段 不知道你看懂了没
id name parentid
1 湖北 0
2 湖南 0
3 武汉 1
4 仙桃 1
5 长沙 2
6 蔡甸 3 1. create function c_tree(@initid int)/*定义函数c_tree,输入参数为初始节点id*/
2. returns @t table(id int,name varchar(100),parentid int,lev int)/*定义表t用来存放取出的数据*/
3. begin
4. declare @i int/*标志递归级别*/
5. set @i=1
6. insert @t select id,name,parentid,@i from tb_city where id=@initid
7. while @@rowcount<>0
8. begin
9. set @i=@i+1
10. insert @t select a.id,a.name,a.parentid,@i from tb_city as a,@t as b
11. where b.id=a.parentid and b.lev=@i-1
12. end
13. return
14. end
你的create function c_tree(@initid int)/*定义函数c_tree,输入参数为初始节点id*/
returns @t table(id int,name varchar(100),parentid int,lev int)/*定义表t用来存放取出的数据*/
begin
declare @i int/*标志递归级别*/
set @i=1
insert @t select id,name,pid ,@i from tb where id=@initid
while @@rowcount<>0
begin
set @i=@i+1
insert @t select a.id,a.name,a.pid ,@i from tb as a,@t as b
where b.id=a.pid and b.lev=@i-1
end
return
end
1 0 a
2 0 b
3 1 c
4 3 d
5 4 e要求 查出来的结果a c d e
真的没看懂吗
我见网上的都是教SQL 语句的。
insert into @table
select 1,0 union all
select 2,1 union all
select 3,1 union all
select 4,2 union all
select 5,3 union all
select 6,4 union all
select 7,5;
WITH Maco
AS ( SELECT A ,
B
FROM @table
WHERE A = 1
UNION ALL
SELECT a.A ,
a.B
FROM @table AS a ,
Maco AS b
WHERE a.B = b.A
)
SELECT *
FROM Maco
DECLARE @Table TABLE(Id int,Pid int,Name char)INSERT INTO @Table
SELECT 1,0,'a' UNION ALL
SELECT 2,0,'b' UNION ALL
SELECT 3,1,'c' UNION ALL
SELECT 4,3,'d' UNION ALL
SELECT 5,4,'e' ;WITH List AS(
SELECT * FROM @Table T WHERE T.Name='a'
UNION ALL
SELECT T2.* FROM List T , @Table T2 WHERE T.Id=T2.Pid
)SELECT L.* FROM List L/*
Id Pid Name
----------- ----------- ----
1 0 a
3 1 c
4 3 d
5 4 e(4 行受影响)
*/
select * from tablename start with name='e' connect by prior pid=id