这是网上代码:
=======
--生成测试数据
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
go
--创建用户定义函数
create function f_getChild(@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 PID = @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.PID=b.ID and b.Level = @i-1
end
return
end
go
--执行查询
select ID from dbo.f_getChild(3)
go
--输出结果
/*
5
6
7
*/
--删除测试数据
drop function f_getChild
drop table BOM
===============
基本可以用 但是排序不是我想要的
=======
--生成测试数据
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
go
--创建用户定义函数
create function f_getChild(@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 PID = @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.PID=b.ID and b.Level = @i-1
end
return
end
go
--执行查询
select ID from dbo.f_getChild(3)
go
--输出结果
/*
5
6
7
*/
--删除测试数据
drop function f_getChild
drop table BOM
===============
基本可以用 但是排序不是我想要的
a b
a c
b d
b e
===
按上面的函数 排序是
a b
a c
b d
b e
===================
我想要的排序结果是
a b
b d
b e
a c
==================
也就是树的最左边查询 专业讲好像是左递归还是什么?
请赐教
a b
b d
b e
a c
==================
这样的结果是按什么排序的啊
select * FROM TREE
start with parentId = '0'
connect by prior currentId = parentId
--创建用户定义函数
ALTER function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT,Sort varchar(7900))
as
begin
declare @i int,@ret varchar(8000)
set @i = 1
insert into @t select ID,PID,@i ,cast(ID as varchar(400)) from BOM where PID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.PID,@i,b.Sort+cast(a.ID as varchar(400))
from
BOM a,@t b
where
a.PID=b.ID and b.Level = @i-1
end
return
end
go
select b.ID from BOM,dbo.f_getChild(3) b where BOM.ID=b.ID ORDER BY b. Sort desc
go