100%结贴率 游标实现BOM多级查询

[sadikaqy]

USE db
go
if object_id('#t1') IS NOT NULL
drop table #t1
go

create table #t1
(
parentID varchar(40),
ID varchar(40),
Lever int,
Name varchar(64)
)

第一层：
Insert into #t values(‘2012’,’909’,1,’ 气缸盖总成’)
第二层：
Insert into #t values(‘909’,’929’,2,’ 阀体’)
Insert into #t values(‘909’,’1709’,2,’ 方螺母2’)
第三层：
Insert into #t values(‘929’,’ 1711’,3,’ 阀’)


第一层：
parentID ID lever name
2012 909 1 气缸盖总成

第二层：
parentID ID lever name
909 929 2 阀体
909 1709 2 方螺母2

第三层：
parentID ID lever name
929 1711 3 阀

想要显示如下：
parentID ID lever name
2012 909 1 气缸盖总成
2012 929 2 阀体
2012 1709 2 方螺母2
2012 1711 3 阀

[唐诗三百首]

SQL 2005起,建议用with .. as ..做递归连接即可,如1楼回复.

[htl258_Tony]

if object_id('tempdb..#t') IS NOT NULL
drop table tempdb..#t
go

create table #t
(
parentID varchar(40),
ID varchar(40),
Lever int,
Name varchar(64)
)

--第一层：
Insert into #t values('2012','909',1,'气缸盖总成')
--第二层：
Insert into #t values('909','929',2,'阀体')
Insert into #t values('909','1709',2,'方螺母2')
--第三层：
Insert into #t values('929','1711',3,'阀')

--SELECT * FROM #t

;WITH t AS (
	SELECT parent=parentid,* FROM #t WHERE lever=1
	UNION ALL
	SELECT parent,b.* FROM t a JOIN #t b ON a.id=b.parentID
)
SELECT parentid=parent,id,lever,name FROM t
/*
parentid	id	lever	name
2012	909	1	气缸盖总成
2012	929	2	阀体
2012	1709	2	方螺母2
2012	1711	3	阀
*/

[--小F--]

-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-09-30 08:52:38
set nocount on
if object_id('tb','U')is not null drop table tb
go
create table tb(ID int, ParentID int)
insert into tb select 1,0  
insert into tb select 2,1  
insert into tb select 3,1  
insert into tb select 4,2  
insert into tb select 5,3  
insert into tb select 6,5  
insert into tb select 7,6
-->Title:查找指定節點下的子結點
if object_id('Uf_GetChildID')is not null drop function Uf_GetChildID
go
create function Uf_GetChildID(@ParentID int)
returns @t table(ID int)
as
begin
   insert @t select ID from tb where ParentID=@ParentID
   while @@rowcount<>0
   begin
      insert @t select a.ID from tb a inner join @t b
      on a.ParentID=b.id and 
      not exists(select 1 from @t where id=a.id)
   end 
return
end
go
select * from dbo.Uf_GetChildID(5)
/*
ID
-----------
6
7
*/
-->Title:查找指定節點的所有父結點
if object_id('Uf_GetParentID')is not null drop function Uf_GetParentID
go
create function Uf_GetParentID(@ID int)
returns @t table(ParentID int)
as
begin
   insert @t select ParentID from tb where ID=@ID
   while @@rowcount!=0
   begin
     insert @t select a.ParentID from tb a inner join @t b
       on a.id=b.ParentID and 
       not exists(select 1 from @t where ParentID=a.ParentID)
   end
  return
end
go
select * from dbo.Uf_GetParentID(2)
/*
ParentID
-----------
1
0
*/



本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/wufeng4552/archive/2009/09/30/4619995.aspx

修改下这个函数就可以了

[AcHerat]

create table tb
(
parentid varchar(40),
id varchar(40),
lever int,
name varchar(64)
)

--第一层：
insert into tb values('2012','909',1,'气缸盖总成')
--第二层：
insert into tb values('909','929',2,'阀体')
insert into tb values('909','1709',2,'方螺母2')
--第三层：
insert into tb values('929','1711',3,'阀')
go

create function f_getid(@id varchar(40))
returns @t table(parentid varchar(40),id varchar(40),lever int,name varchar(64))
as
begin
   insert @t select parentid,id,lever,name from tb where id = @id
   while @@rowcount<>0
   begin
      insert @t select b.parentid,a.id,a.lever,a.name from tb a inner join @t b
      on a.parentid = b.id and 
      not exists(select 1 from @t where id = a.id)
   end 
return
end
go
select * from dbo.f_getid('909')


drop function dbo.f_getid
drop table tb

/*

parentid                                 id                                       lever       name
---------------------------------------- ---------------------------------------- ----------- ----------------------------------------------------------------
2012                                     909                                      1           气缸盖总成
2012                                     929                                      2           阀体
2012                                     1709                                     2           方螺母2
2012                                     1711                                     3           阀

(4 行受影响)

[我是无聊至极的人]

create table tb
(
parentid varchar(40),
id varchar(40),
lever int,
name varchar(64)
)

--第一层：
insert into tb values('2012','909',1,'气缸盖总成')
--第二层：
insert into tb values('909','929',2,'阀体')
insert into tb values('909','1709',2,'方螺母2')
--第三层：
insert into tb values('929','1711',3,'阀')
go

create function f_getid(@id varchar(40))
returns @t table(parentid varchar(40),id varchar(40),lever int,name varchar(64))
as
begin
insert @t select parentid,id,lever,name from tb where id = @id
while @@rowcount<>0
begin
insert @t select b.parentid,a.id,a.lever,a.name from tb a inner join @t b
on a.parentid = b.id and
not exists(select 1 from @t where id = a.id)
end
return
end
go
select * from dbo.f_getid('909')


drop function dbo.f_getid
drop table tb