一个查询出树形结构下级或上级的SQL语句的书写问题

yumanqing 2011-11-24 10:47:49

建立SQL环境的语句：





CREATE TABLE [dbo].[tbNode_List] (

	[nlID] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL PRIMARY KEY,--公司编码

	[OwnerNodeID] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,  --公司名称

	[nlName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,  --上级公司编码

	[nlAddress] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL   --地址

	) ON [PRIMARY]

GO



INSERT INTO tbNode_List(nlID,OwnerNodeID,nlName,nlAddress) VALUES('0001','0','某公司总部','北京')

INSERT INTO tbNode_List(nlID,OwnerNodeID,nlName,nlAddress) VALUES('0002','0001','西北营销中心','')

INSERT INTO tbNode_List(nlID,OwnerNodeID,nlName,nlAddress) VALUES('0003','0001','北京公司','')

INSERT INTO tbNode_List(nlID,OwnerNodeID,nlName,nlAddress) VALUES('0004','0003','北京海淀公司','')

INSERT INTO tbNode_List(nlID,OwnerNodeID,nlName,nlAddress) VALUES('0005','0003','北京昌平公司','')

INSERT

现在想写一SQL语句，比如给一个公司编码为：0003
我可以查询出该公司的上级公司：0001，0002
和下级公司：0004，0005，0006

好像是递归查找，不知道怎么写，哪位大侠做过类似的，请指点一下，谢谢。

...全文

802 18 打赏收藏转发到动态举报

写回复

用AI写文章

18 条回复

切换为时间正序

请发表友善的回复…

发表回复

yumanqing 2011-11-24

打赏
举报

AcHerat

(小三 [兔子党党务院院长])

回复的就是我想要的查询结果，就是还有些看不太明白，要多学习学习，SQL2000中要麻烦很多啊，呵呵

勿勿 2011-11-24

打赏
举报

说出你想要的结果

yumanqing 2011-11-24

打赏
举报

谢谢，我在看看

yumanqing 2011-11-24

打赏
举报

谢谢各位，我在研究研究

AcHerat 元老 2011-11-24

打赏
举报



CREATE TABLE [dbo].[tbNode_List] (

    [nlID] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL PRIMARY KEY,--公司编码

    [OwnerNodeID] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,  --公司名称

    [nlName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,  --上级公司编码

    [nlAddress] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL   --地址

    ) ON [PRIMARY]

GO



INSERT INTO tbNode_List(nlID,OwnerNodeID,nlName,nlAddress) VALUES('0001','0','某公司总部','北京')

INSERT INTO tbNode_List(nlID,OwnerNodeID,nlName,nlAddress) VALUES('0002','0001','西北营销中心','')

INSERT INTO tbNode_List(nlID,OwnerNodeID,nlName,nlAddress) VALUES('0003','0001','北京公司','')

INSERT INTO tbNode_List(nlID,OwnerNodeID,nlName,nlAddress) VALUES('0004','0003','北京海淀公司','')

INSERT INTO tbNode_List(nlID,OwnerNodeID,nlName,nlAddress) VALUES('0005','0003','北京昌平公司','')

go



create function f_getP(@nlID varchar(10)) 

returns @re table(nlID varchar(10),lev int,ownerNodeID varchar(10),TFlag varchar(10)) 

as

begin

    declare @l int 

    set @l=0 

    insert @re select nlID,@l,ownerNodeID,'本级nlID' from tbNode_List where nlID = @nlID

    while @@rowcount>0 

    begin 

		set @l=@l+1

		insert @re select a.nlID,@l,a.ownerNodeID,'下级nlID'

		from tbNode_List a,@re b

		where a.ownerNodeID=b.nlID and b.lev=@l-1 and a.nlID is not null

    end 

    update @re set lev=@l-lev

    return

end

go



create function f_getC(@nlID varchar(10)) 

returns @re table(nlID varchar(10),lev int,ownerNodeID varchar(10),TFlag varchar(10)) 

as

begin

    declare @l int 

    set @l=0 

    insert @re select nlID,@l,ownerNodeID,'本级nlID' from tbNode_List where nlID = @nlID

    while @@rowcount>0 

    begin 

		set @l=@l+1

		insert @re select a.nlID,@l,a.ownerNodeID,'上级nlID'

		from tbNode_List a,@re b

		where a.nlID=b.ownerNodeID and b.lev=@l-1 and a.ownerNodeID is not null

    end 

    update @re set lev=@l-lev

    return

end

go



declare @nlID varchar(10)

set @nlID = '0003'



select * from dbo.f_getP(@nlID)

union

select * from dbo.f_getC(@nlID)



drop function f_getP,f_getC

drop table [tbNode_List]



/***************



nlID       lev         ownerNodeID TFlag

---------- ----------- ----------- ----------

0001       1           0           上级nlID

0003       2           0001        本级nlID

0004       1           0003        下级nlID

0005       1           0003        下级nlID



(4 行受影响)

yumanqing 2011-11-24

打赏
举报

是SQL SERVER 2000

AcHerat 元老 2011-11-24

打赏
举报

SQL2000 ？

yumanqing 2011-11-24

打赏
举报

[Quote=引用 9 楼 acherat 的回复:]
SQL code

select * from cte
union
select * from cta

/**************

nlID ownerNodeID TFlag
---------- ----------- --------
0001 0 上级nlID
0003 0001 本级nlI……
[/Quote]

感谢，
但我执行怎么报语法错误呢：

服务器: 消息 156，级别 15，状态 1，行 4
在关键字 'with' 附近有语法错误。
服务器: 消息 170，级别 15，状态 1，行 10
第 10 行: ',' 附近有语法错误。

AcHerat 元老 2011-11-24

打赏
举报



select * from cte

union

select * from cta



/**************



nlID       ownerNodeID TFlag

---------- ----------- --------

0001       0           上级nlID

0003       0001        本级nlID

0004       0003        下级nlID

0005       0003        下级nlID



(4 行受影响)

AcHerat 元老 2011-11-24

打赏
举报



CREATE TABLE [dbo].[tbNode_List] (

    [nlID] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL PRIMARY KEY,--公司编码

    [OwnerNodeID] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,  --公司名称

    [nlName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,  --上级公司编码

    [nlAddress] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL   --地址

    ) ON [PRIMARY]

GO



INSERT INTO tbNode_List(nlID,OwnerNodeID,nlName,nlAddress) VALUES('0001','0','某公司总部','北京')

INSERT INTO tbNode_List(nlID,OwnerNodeID,nlName,nlAddress) VALUES('0002','0001','西北营销中心','')

INSERT INTO tbNode_List(nlID,OwnerNodeID,nlName,nlAddress) VALUES('0003','0001','北京公司','')

INSERT INTO tbNode_List(nlID,OwnerNodeID,nlName,nlAddress) VALUES('0004','0003','北京海淀公司','')

INSERT INTO tbNode_List(nlID,OwnerNodeID,nlName,nlAddress) VALUES('0005','0003','北京昌平公司','')

go



declare @nlID varchar(10)

set @nlID = '0003'



;with cte as

(

	select nlID,ownerNodeID,'本级nlID' as TFlag from [tbNode_List] where nlID = @nlID

	union all

	select a.nlID,a.ownerNodeID,'下级nlID'

	from [tbNode_List] a join cte as f on a.ownerNodeID = f.nlID

),cta as

(

	select nlID,ownerNodeID,'本级nlID' as TFlag from [tbNode_List] where nlID = @nlID

	union all

	select a.nlID,a.ownerNodeID,'上级nlID'

	from [tbNode_List] a join cta as f on f.ownerNodeID = a.nlID

	where a.ownerNodeID is not null

)



select * from cte

union all

select * from cta



drop table [tbNode_List]



/********************



nlID       ownerNodeID TFlag

---------- ----------- --------

0003       0001        本级nlID

0004       0003        下级nlID

0005       0003        下级nlID

0003       0001        本级nlID

0001       0           上级nlID



(5 行受影响)

--小F-- 2011-11-24

打赏
举报

记得一年之前 BOM的没几个人回现在一见BOM 好多人直接就回了啊哈哈

中国风 2011-11-24

打赏
举报

GO

if object_id('F_BOM','FN') is not null

    drop function F_BOM

go

create function F_BOM(@nlID nvarchar(50))

returns nvarchar(1000)

as

begin

    declare @s nvarchar(1000),@Name nvarchar(50),@i int

    set @i=0

    lab:

    set @i=@i+1

    set @Name =(select [OwnerNodeID] from tbNode_List where [nlID]=@nlID)

    select @nlID=[OwnerNodeID] from tbNode_List where [nlID]=@nlID



    if @Name is not NULL AND @Name<>'0'

		begin

			set @s=isnull(@s+',','')+@Name

			goto lab

		end

     

    return @s

end

GO

SELECT dbo.f_BOM('0003')



/*

0001

*/

昵称被占用了 2011-11-24

打赏
举报

;WITH CTE AS (

SELECT nlID = '0003','本身' AS TYPE

UNION ALL

SELECT nlID,'下级' AS TYPE

FROM [dbo].[tbNode_List] AS A,CTE AS B

WHERE A.OwnerNodeID = B.nlID 

AND B.TYPE IN ('本身','下级')

UNION ALL

SELECT OwnerNodeID AS nlID,'上级' AS TYPE

FROM [dbo].[tbNode_List] AS A,CTE AS B

WHERE A.nlID = B.nlID

AND B.TYPE IN ('本身','上级')

)

SELECT * FROM CTE

pengxuan 2011-11-24

打赏
举报

公用表表达式的递归用法



--查找上级

with cte as 

(

 select * from tbNode_List where nlID='0003'

 union all

 select a.* from tbNode_List a inner join cte b on a.nlID=b.OwnerNodeID

)

select * from cte where nlID<>'0003'



--查找下级

with cte as 

(

 select * from tbNode_List where nlID='0003'

 union all

 select a.* from tbNode_List a inner join cte b on a.OwnerNodeID=b.nlID

)

select * from cte where nlID<>'0003'

中国风 2011-11-24

打赏
举报

參照
這一貼回復過類似問題
http://topic.csdn.net/u/20111123/02/3379b7a5-41b3-46f2-868e-f452dde5e540.html

中国风 2011-11-24

打赏
举报

0003/0002不是上下級是平級關係

AcHerat 元老 2011-11-24

打赏
举报



-- 使用函数的方法：



--建立 演示环境



if object_id('tb_bookInfo') is not null drop table tb_bookInfo

go

create table tb_bookInfo(number int,name varchar(10),type int)

insert tb_bookInfo

select 1 ,'n1', 6 union all

select 2 ,'n2', 3





if object_id('tb_bookType') is not null drop table tb_bookType

go

create table tb_bookType(id int,typeName varchar(10),parentid int)

insert tb_bookType

select 1,'英语',0 union all

select 2,'生物',0 union all

select 3,'计算机',0 union all

select 4,'口语',1 union all

select 5,'听力',1 union all

select 6,'数据库',3 union all

select 7,'软件工程',3 union all

select 8,'SQL Server',6



select a.*,b.level from tb_bookInfo  a,f_getC(3) b  where a.type=b.id  order by b.level 

/*

number      name       type        level      

----------- ---------- ----------- -----------

2           n2         3           0

1           n1         6           1



（所影响的行数为 2 行）

*/

--查所有父结点

if object_id('f_getP') is not null drop function f_getP

go

create function f_getP(@id int) 

returns @re table(id int,level int) 

as 

begin

    declare @l int 

    set @l=0 

    insert @re select @id,@l 

    while @@rowcount>0 

    begin 

 set @l=@l+1

 insert @re select a.parentid,@l from tb_bookType a,@re b

 where a.id=b.id and b.level=@l-1 and a.parentid<>0

    end 

    update @re set level=@l-level

    return 

end 

go 





--查所有子结点

if object_id('f_getC') is not null drop function f_getC

go

create function f_getC(@id int) 

returns @re table(id int,level int) 

as 

begin

    declare @l int 

    set @l=0 

    insert @re select @id,@l 

    while @@rowcount>0

    begin 

        set @l=@l+1

        insert @re select a.id,@l from tb_bookType as a,@re as b 

 where b.id=a.parentid and b.level=@l-1

    end

    return 

end 

go 



--查所有父子结点

if object_id('f_getAll') is not null drop function f_getAll

go

create function f_getAll(@id int) 

returns @re table(id int,level int) 

as 

begin 

    declare @l int 

    set @l=0 

    insert @re select @id,@l 

    while @@rowcount>0 

    begin 

 set @l=@l+1

 insert @re select a.parentid,@l from tb_bookType a,@re b

 where a.id=b.id and b.level=@l-1 and a.parentid<>0

    end 

    update @re set level=@l-level 

    while @@rowcount>0

    begin 

        set @l=@l+1

        insert @re select a.id,@l from tb_bookType as a,@re as b 

 where b.id=a.parentid and b.level=@l-1

    end

    return

end 

go  

 



--删除演示



drop table tb_bookInfo



drop table tb_bookType



drop function f_getP



drop function f_getC

drop function f_getAll

GO



--sqlserver2005的新方法



-- 建立演示环境

IF OBJECT_ID('[Dept]') IS NOT NULL

    DROP TABLE [Dept]

GO

CREATE TABLE Dept(

 id int PRIMARY KEY,

 parent_id int,

 name nvarchar(20))

INSERT Dept

SELECT 1, 0, N'财务部' UNION ALL

SELECT 2, 0, N'行政部' UNION ALL

SELECT 3, 0, N'业务部' UNION ALL

SELECT 4, 0, N'业务部' UNION ALL

SELECT 5, 4, N'销售部' UNION ALL

SELECT 6, 4, N'MIS' UNION ALL

SELECT 7, 6, N'UI' UNION ALL

SELECT 8, 6, N'软件开发' UNION ALL

SELECT 9, 8, N'内部开发'

GO

--1、父-〉子

-- 查询指定部门下面的所有部门

DECLARE @Dept_name nvarchar(20)

SET @Dept_name = N'MIS'

;WITH

DEPTS AS(

 -- 定位点成员

 SELECT * FROM Dept WHERE name = @Dept_name

 UNION ALL

 -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归

 SELECT A.*  FROM Dept A, DEPTS B  WHERE A.parent_id = B.id

)

SELECT * FROM DEPTS

GO

--结果如下

/*

id          parent_id   name                

----------- ----------- --------------------

6           4           MIS

7           6           UI

8           6           软件开发

9           8           内部开发



（所影响的行数为 4 行）

*/



--2、子-〉父

-- 查询指定部门下面的所有部门

DECLARE @Dept_name nvarchar(20)

SET @Dept_name = N'内部开发'

;WITH

DEPTS AS(

 -- 定位点成员

  SELECT * FROM Dept WHERE name = @Dept_name

--SELECT d.id,d.parent_id,d.name,convert(nvarchar(50),d.name) as parent  FROM Dept where @Dept_name

 UNION ALL

 -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归

 SELECT a.* FROM Dept a, DEPTS b WHERE a.id = b.parent_id

)

SELECT * FROM DEPTS

GO



--结果如下

/*

id          parent_id   name                

----------- ----------- --------------------

9           8           内部开发

8           6           软件开发

6           4           MIS

4           0           业务部



（所影响的行数为 4 行）

*/



-- 删除演示环境

DROP TABLE Dept