本代码实现
怎样在树形结构中查询出指定第几级父级以下的所有ID号
具体点说就是假设现在有6级,我想查出我父级中前4级中所有ID号,请高手进入!
参考以下MSSQL下能运行得代码谁能帮忙出个MYSQL版本么:
--生成测试数据
create table BOM(ID INT,PID INT,MSG VARCHAR(1000))
insert into BOM select 1, 0,NULL
insert into BOM select 2, 1,NULL
insert into BOM select 3, 1,NULL
insert into BOM select 4, 2,NULL
insert into BOM select 5, 3,NULL
insert into BOM select 6, 5,NULL
insert into BOM select 7, 6,NULL
insert into BOM select 8, 7,NULL
insert into BOM select 9, 8,NULL
insert into BOM select 10, 9,NULL
insert into BOM select 11,10,NULL
go
--创建用户定义函数
create function f_getParent(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
declare @i int,@ret varchar(8000)
set @i = 1
insert into @t select ID,PID,@i from BOM where ID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.PID,@i
from
BOM a,@t b
where
a.ID=b.PID and b.Level = @i-1
end
return
end
go
--执行查询
select ID from dbo.f_getParent(11) order by Level desc
--输出结果
/*
ID
----
1
3
5
6
7
8
9
10
11
*/
--执行查询,选择最靠近子节点的4个父节点
select top 4 ID from dbo.f_getParent(11) order by Level
go
--输出结果
/*
ID
----
11
10
9
8
*/
--删除测试数据
drop function f_getParent
drop table BOM
怎样在树形结构中查询出指定第几级父级以下的所有ID号
具体点说就是假设现在有6级,我想查出我父级中前4级中所有ID号,请高手进入!
参考以下MSSQL下能运行得代码谁能帮忙出个MYSQL版本么:
--生成测试数据
create table BOM(ID INT,PID INT,MSG VARCHAR(1000))
insert into BOM select 1, 0,NULL
insert into BOM select 2, 1,NULL
insert into BOM select 3, 1,NULL
insert into BOM select 4, 2,NULL
insert into BOM select 5, 3,NULL
insert into BOM select 6, 5,NULL
insert into BOM select 7, 6,NULL
insert into BOM select 8, 7,NULL
insert into BOM select 9, 8,NULL
insert into BOM select 10, 9,NULL
insert into BOM select 11,10,NULL
go
--创建用户定义函数
create function f_getParent(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
declare @i int,@ret varchar(8000)
set @i = 1
insert into @t select ID,PID,@i from BOM where ID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.PID,@i
from
BOM a,@t b
where
a.ID=b.PID and b.Level = @i-1
end
return
end
go
--执行查询
select ID from dbo.f_getParent(11) order by Level desc
--输出结果
/*
ID
----
1
3
5
6
7
8
9
10
11
*/
--执行查询,选择最靠近子节点的4个父节点
select top 4 ID from dbo.f_getParent(11) order by Level
go
--输出结果
/*
ID
----
11
10
9
8
*/
--删除测试数据
drop function f_getParent
drop table BOM
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货