当条件=1的时候其下面的子节点就层数增加一层, 比如 节点 父节点 条件 需要求的层(可以用递归求) A NULL NULL NULL B A 1 1(A下的所有直接节点都默认为1) F B 1 2(因为F是跟着B的,B的条件为1,满足,所以F的层值就为1+1=2) C A 0 1(A下的所有直接节点都默认为1) D C 1 1(因为D是跟着C的,C的条件为0,不满足,所以C还是1) E D 1 2(因为E是跟着D的,D的条件为1,满足,所以E的层值就为1+1=2) G E 1 3(因为G是跟着E的,E的条件为1,满足,所以E的层值就为1+2=3) 这样说大家明白了吗?
--查询处理函数 create function f_level() returns @t table(id int,sid varchar(8000),排列层 int,条件 int,gid int) as begin declare @l int set @l=0 insert @t select id,id,null,条件,0 from tb where level=0 while @@rowcount>0 begin set @l=@l+1 insert @t select a.id,b.sid+','+cast(a.id as varchar),case @l when 1 then 1 else 0 end,a.条件,0 from tb a,@t b where a.pid=b.id and a.level=@l end
insert @t select id,sid,排列层,条件,1 from @t order by sid delete from @t where gid=0 update @t set @l=case 排列层 when 1 then 1 else case 条件 when 1 then @l+1 else @l end end,排列层=@l where 排列层 is not null
return end go--调用函数实现楼主需要的查询 select a.ID,a.PID,实际层=a.level,a.条件,b.排列层 from tb a,f_level() b where a.id=b.id order by b.sid go
--示例--示例数据 create table tb(ID int,PID int,level int,条件 int,要求排列层 int) insert tb select 2001, NULL, 0, NULL, NULL union all select 2002, 2001, 1, 0, NULL union all select 2003, 2002, 2, 1, NULL union all select 2004, 2003, 3, 1, NULL union all select 2005, 2004, 4, 0, NULL union all select 2006, 2005, 5, 1, NULL union all select 2007, 2006, 6, 0, NULL union all select 2102, 2001, 1, 1, NULL union all select 2103, 2102, 2, 0, NULL union all select 2104, 2103, 3, 1, NULL union all select 2105, 2104, 4, 1, NULL go--查询处理函数 create function f_level() returns @t table(id int,sid varchar(8000),排列层 int,条件 int,gid int) as begin declare @l int set @l=0 insert @t select id,id,null,条件,0 from tb where level=0 while @@rowcount>0 begin set @l=@l+1 insert @t select a.id,b.sid+','+cast(a.id as varchar),case @l when 1 then 1 else 0 end,a.条件,0 from tb a,@t b where a.pid=b.id and a.level=@l end
insert @t select id,sid,排列层,条件,1 from @t order by sid delete from @t where gid=0 update @t set @l=case 排列层 when 1 then 1 else case 条件 when 1 then @l+1 else @l end end,排列层=@l where 排列层 is not null
return end go--调用函数实现楼主需要的查询 select a.ID,a.PID,实际层=a.level,a.条件,b.排列层 from tb a,f_level() b where a.id=b.id order by b.sid go--删除测试 drop table tb drop function f_level/*--测试结果ID PID 实际层 条件 排列层 ----------- ----------- ----------- ----------- ----------- 2001 NULL 0 NULL NULL 2002 2001 1 0 1 2003 2002 2 1 2 2004 2003 3 1 3 2005 2004 4 0 3 2006 2005 5 1 4 2007 2006 6 0 4 2102 2001 1 1 1 2103 2102 2 0 1 2104 2103 3 1 2 2105 2104 4 1 3(所影响的行数为 11 行) --*/
--示例--示例数据 create table tb(ID int,PID int,level int,条件 int,要求排列层 int) insert tb select 2001, NULL, 0, NULL, NULL union all select 2002, 2001, 1, 0, NULL union all select 2003, 2002, 2, 1, NULL union all select 2004, 2003, 3, 1, NULL union all select 2005, 2004, 4, 0, NULL union all select 2006, 2005, 5, 1, NULL union all select 2007, 2006, 6, 0, NULL union all select 2102, 2001, 1, 1, NULL union all select 2103, 2102, 2, 0, NULL union all select 2104, 2103, 3, 1, NULL union all select 2105, 2104, 4, 1, NULL go--查询处理函数 create function f_level() returns @t table(id int,sid varchar(8000),排列层 int,条件 int) as begin declare @l int set @l=0 insert @t select id,id,null,条件 from tb where level=0 while @@rowcount>0 begin set @l=@l+1 insert @t select a.id,b.sid+','+cast(a.id as varchar) ,case when @l=1 then 1 else case when b.条件=1 then b.排列层+1 else b.排列层 end end,a.条件 from tb a,@t b where a.pid=b.id and a.level=@l end return end go--调用函数实现楼主需要的查询 select a.ID,a.PID,实际层=a.level,a.条件,b.排列层 from tb a,f_level() b where a.id=b.id order by b.sid go--删除测试 drop table tb drop function f_level/*--测试结果ID PID 实际层 条件 排列层 ----------- ----------- ----------- ----------- ----------- 2001 NULL 0 NULL NULL 2002 2001 1 0 1 2003 2002 2 1 1 2004 2003 3 1 2 2005 2004 4 0 3 2006 2005 5 1 3 2007 2006 6 0 4 2102 2001 1 1 1 2103 2102 2 0 2 2104 2103 3 1 2 2105 2104 4 1 3(所影响的行数为 11 行) --*/
比如
节点 父节点 条件 需要求的层(可以用递归求)
A NULL NULL NULL
B A 1 1(A下的所有直接节点都默认为1)
F B 1 2(因为F是跟着B的,B的条件为1,满足,所以F的层值就为1+1=2)
C A 0 1(A下的所有直接节点都默认为1)
D C 1 1(因为D是跟着C的,C的条件为0,不满足,所以C还是1)
E D 1 2(因为E是跟着D的,D的条件为1,满足,所以E的层值就为1+1=2)
G E 1 3(因为G是跟着E的,E的条件为1,满足,所以E的层值就为1+2=3)
这样说大家明白了吗?
请问楼上五星级高手 在SQL里怎么用递归实现 或者有其他效率更高的方法?
create function f_level()
returns @t table(id int,sid varchar(8000),排列层 int,条件 int,gid int)
as
begin
declare @l int
set @l=0
insert @t select id,id,null,条件,0
from tb where level=0
while @@rowcount>0
begin
set @l=@l+1
insert @t select a.id,b.sid+','+cast(a.id as varchar),case @l when 1 then 1 else 0 end,a.条件,0
from tb a,@t b
where a.pid=b.id and a.level=@l
end
insert @t select id,sid,排列层,条件,1 from @t order by sid
delete from @t where gid=0
update @t set @l=case 排列层 when 1 then 1 else case 条件 when 1 then @l+1 else @l end end,排列层=@l
where 排列层 is not null
return
end
go--调用函数实现楼主需要的查询
select a.ID,a.PID,实际层=a.level,a.条件,b.排列层
from tb a,f_level() b
where a.id=b.id
order by b.sid
go
create table tb(ID int,PID int,level int,条件 int,要求排列层 int)
insert tb select 2001, NULL, 0, NULL, NULL
union all select 2002, 2001, 1, 0, NULL
union all select 2003, 2002, 2, 1, NULL
union all select 2004, 2003, 3, 1, NULL
union all select 2005, 2004, 4, 0, NULL
union all select 2006, 2005, 5, 1, NULL
union all select 2007, 2006, 6, 0, NULL
union all select 2102, 2001, 1, 1, NULL
union all select 2103, 2102, 2, 0, NULL
union all select 2104, 2103, 3, 1, NULL
union all select 2105, 2104, 4, 1, NULL
go--查询处理函数
create function f_level()
returns @t table(id int,sid varchar(8000),排列层 int,条件 int,gid int)
as
begin
declare @l int
set @l=0
insert @t select id,id,null,条件,0
from tb where level=0
while @@rowcount>0
begin
set @l=@l+1
insert @t select a.id,b.sid+','+cast(a.id as varchar),case @l when 1 then 1 else 0 end,a.条件,0
from tb a,@t b
where a.pid=b.id and a.level=@l
end
insert @t select id,sid,排列层,条件,1 from @t order by sid
delete from @t where gid=0
update @t set @l=case 排列层 when 1 then 1 else case 条件 when 1 then @l+1 else @l end end,排列层=@l
where 排列层 is not null
return
end
go--调用函数实现楼主需要的查询
select a.ID,a.PID,实际层=a.level,a.条件,b.排列层
from tb a,f_level() b
where a.id=b.id
order by b.sid
go--删除测试
drop table tb
drop function f_level/*--测试结果ID PID 实际层 条件 排列层
----------- ----------- ----------- ----------- -----------
2001 NULL 0 NULL NULL
2002 2001 1 0 1
2003 2002 2 1 2
2004 2003 3 1 3
2005 2004 4 0 3
2006 2005 5 1 4
2007 2006 6 0 4
2102 2001 1 1 1
2103 2102 2 0 1
2104 2103 3 1 2
2105 2104 4 1 3(所影响的行数为 11 行)
--*/
----------- ----------- ----------- ----------- -----------
2001 NULL NULL NULL
2002 2001 0 1
2003 2002 1 2*2003接2002,2002条件为0,所以该层应为1
2004 2003 1 3*2004接2003,2003条件为1,所以该层应为2
2005 2004 0 3*2005接2004,2004条件为1,所以该层为3正确
2006 2005 1 4
2102 2001 1 1
2103 2102 0 1
2104 2103 1 2
2105 2104 1 3
create table tb(ID int,PID int,level int,条件 int,要求排列层 int)
insert tb select 2001, NULL, 0, NULL, NULL
union all select 2002, 2001, 1, 0, NULL
union all select 2003, 2002, 2, 1, NULL
union all select 2004, 2003, 3, 1, NULL
union all select 2005, 2004, 4, 0, NULL
union all select 2006, 2005, 5, 1, NULL
union all select 2007, 2006, 6, 0, NULL
union all select 2102, 2001, 1, 1, NULL
union all select 2103, 2102, 2, 0, NULL
union all select 2104, 2103, 3, 1, NULL
union all select 2105, 2104, 4, 1, NULL
go--查询处理函数
create function f_level()
returns @t table(id int,sid varchar(8000),排列层 int,条件 int)
as
begin
declare @l int
set @l=0
insert @t select id,id,null,条件
from tb where level=0
while @@rowcount>0
begin
set @l=@l+1
insert @t select a.id,b.sid+','+cast(a.id as varchar)
,case when @l=1 then 1
else case when b.条件=1 then b.排列层+1
else b.排列层 end end,a.条件
from tb a,@t b
where a.pid=b.id and a.level=@l
end
return
end
go--调用函数实现楼主需要的查询
select a.ID,a.PID,实际层=a.level,a.条件,b.排列层
from tb a,f_level() b
where a.id=b.id
order by b.sid
go--删除测试
drop table tb
drop function f_level/*--测试结果ID PID 实际层 条件 排列层
----------- ----------- ----------- ----------- -----------
2001 NULL 0 NULL NULL
2002 2001 1 0 1
2003 2002 2 1 1
2004 2003 3 1 2
2005 2004 4 0 3
2006 2005 5 1 3
2007 2006 6 0 4
2102 2001 1 1 1
2103 2102 2 0 2
2104 2103 3 1 2
2105 2104 4 1 3(所影响的行数为 11 行)
--*/