求一个 生成树的sql语句

今天客户要求用sql写一个生成一个树型菜单，写不来，想请各位帮忙。
1）想请大家帮忙写一个 存储过程 或者是一个函数来生成。
2）想问一下这样的生成的树，性能和实用性怎么样？

15 个解决方案

#1

http://blog.csdn.net/feixianxxx/archive/2009/11/01/4753783.aspx
参看此链接。。。。

#2

引用 1 楼 feixianxxx 的回复:

http://blog.csdn.net/feixianxxx/archive/2009/11/01/4753783.aspx
参看此链接。。。。

UP 学习

#3

做权限树最少需要五张表，用户表、用户角色表、角色表、权限表、角色权限表
sql语句为：


String sql = "select ps.id, ps.name, ps.url, ps.perparent         from permissions ps, "
+ "rolepermission rp, roles rl, userrole ur, users us "
+ "where ps.id = rp.permissionsid and rl.id = rp.rolesid "
+ "and us.id = ur.usersid and rl.id = ur.rolesid and us.id = ? 

#4

引用楼主 claymore1114 的回复:

今天客户要求用sql写一个生成一个树型菜单，写不来，想请各位帮忙。
1）想请大家帮忙写一个 存储过程 或者是一个函数来生成。
2）想问一下这样的生成的树，性能和实用性怎么样？

/*
标题：SQL SERVER 2000中查询指定节点及其所有子节点的函数(表格形式显示)
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 
时间：2008-05-12
地点：广东深圳
*/

create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null  , '广东省')
insert into tb values('002' , '001' , '广州市')
insert into tb values('003' , '001' , '深圳市')
insert into tb values('004' , '002' , '天河区')
insert into tb values('005' , '003' , '罗湖区')
insert into tb values('006' , '003' , '福田区')
insert into tb values('007' , '003' , '宝安区')
insert into tb values('008' , '007' , '西乡镇')
insert into tb values('009' , '007' , '龙华镇')
insert into tb values('010' , '007' , '松岗镇')
go

--查询指定节点及其所有子节点的函数
create function f_cid(@ID varchar(3)) returns @t_level table(id varchar(3) , level int)
as
begin
  declare @level int
  set @level = 1
  insert into @t_level select @id , @level
  while @@ROWCOUNT > 0
  begin
    set @level = @level + 1
    insert into @t_level select a.id , @level
    from tb a , @t_Level b
    where a.pid = b.id and b.level = @level - 1
  end
  return
end
go

--调用函数查询001(广东省)及其所有子节点
select a.* from tb a , f_cid('001') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
001  NULL 广东省
002  001  广州市
003  001  深圳市
004  002  天河区
005  003  罗湖区
006  003  福田区
007  003  宝安区
008  007  西乡镇
009  007  龙华镇
010  007  松岗镇

（所影响的行数为 10 行）
*/

--调用函数查询002(广州市)及其所有子节点
select a.* from tb a , f_cid('002') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
002  001  广州市
004  002  天河区

（所影响的行数为 2 行）
*/

--调用函数查询003(深圳市)及其所有子节点
select a.* from tb a , f_cid('003') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
003  001  深圳市
005  003  罗湖区
006  003  福田区
007  003  宝安区
008  007  西乡镇
009  007  龙华镇
010  007  松岗镇

（所影响的行数为 7 行）
*/

drop table tb
drop function f_cid

@@ROWCOUNT：返回受上一语句影响的行数。
返回类型：integer。
注释：任何不返回行的语句将这一变量设置为 0 ，如 IF 语句。
示例：下面的示例执行 UPDATE 语句并用 @@ROWCOUNT 来检测是否有发生更改的行。

UPDATE authors SET au_lname = 'Jones' WHERE au_id = '999-888-7777'
IF @@ROWCOUNT = 0
   print 'Warning: No rows were updated'

结果：

(所影响的行数为 0 行)
Warning: No rows were updated

/*
标题：SQL SERVER 2005中查询指定节点及其所有子节点的函数(表格形式显示)
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 
时间：2010-02-02
地点：*乌鲁木齐
*/

create table tb(id varchar(3) , pid varchar(3) , name nvarchar(10))
insert into tb values('001' , null  , N'广东省')
insert into tb values('002' , '001' , N'广州市')
insert into tb values('003' , '001' , N'深圳市')
insert into tb values('004' , '002' , N'天河区')
insert into tb values('005' , '003' , N'罗湖区')
insert into tb values('006' , '003' , N'福田区')
insert into tb values('007' , '003' , N'宝安区')
insert into tb values('008' , '007' , N'西乡镇')
insert into tb values('009' , '007' , N'龙华镇')
insert into tb values('010' , '007' , N'松岗镇')
go

DECLARE @ID VARCHAR(3)

--查询ID = '001'的所有子节点
SET @ID = '001'
;WITH T AS
(
  SELECT ID , PID , NAME 
  FROM TB
  WHERE ID = @ID
  UNION ALL
  SELECT A.ID , A.PID , A.NAME 
  FROM TB AS A JOIN T AS B ON A.PID = B.ID
)
SELECT * FROM T ORDER BY ID
/*
ID   PID  NAME
---- ---- ----------
001  NULL 广东省
002  001  广州市
003  001  深圳市
004  002  天河区
005  003  罗湖区
006  003  福田区
007  003  宝安区
008  007  西乡镇
009  007  龙华镇
010  007  松岗镇

(10 行受影响)
*/

--查询ID = '002'的所有子节点
SET @ID = '002'
;WITH T AS
(
  SELECT ID , PID , NAME 
  FROM TB
  WHERE ID = @ID
  UNION ALL
  SELECT A.ID , A.PID , A.NAME 
  FROM TB AS A JOIN T AS B ON A.PID = B.ID
)
SELECT * FROM T ORDER BY ID
/*
ID   PID  NAME
---- ---- ----------
002  001  广州市
004  002  天河区

(2 行受影响)
*/

--查询ID = '003'的所有子节点
SET @ID = '003'
;WITH T AS
(
  SELECT ID , PID , NAME 
  FROM TB
  WHERE ID = @ID
  UNION ALL
  SELECT A.ID , A.PID , A.NAME 
  FROM TB AS A JOIN T AS B ON A.PID = B.ID
)
SELECT * FROM T ORDER BY ID
/*
ID   PID  NAME
---- ---- ----------
003  001  深圳市
005  003  罗湖区
006  003  福田区
007  003  宝安区
008  007  西乡镇
009  007  龙华镇
010  007  松岗镇

(7 行受影响)
*/

drop table tb

--注：除ID值不一样外，三个SQL语句是一样的。

#5

该回复于2010-03-17 09:02:41被版主删除

#6

老大的博客
树形结构

#7

这个东西在CSDN太常见了，Bom式查询

#8

-->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

#9

--树型结构处理之双编号（广度深度排序）
if OBJECTPROPERTY(object_id('tb'),'isusertable')<>0 
  drop table tb
create table tb(ybh nvarchar(10),ebh nvarchar(10),beizhu nvarchar(1000))
insert tb
select '0001',null,'云南省'
union all select '0002','0001','昆明市'
union all select '0003','0001','昭通市'
union all select '0009','0001','大理市'
union all select '0008',null,'四川省'
union all select '0004',null,'贵州省'
union all select '0005','0002','五华区'
union all select '0007','0002','水富县'
union all select '0006','0005','西园路192号'
union all select '0010','0006','金色梧桐3-702'
union all select '0011','0010','昆明越科时空科技有限公司'
union all select '0015','0007','两碗乡'
union all select '0013','0015','两碗村'
union all select '0012','0013','某跨国集团董事长'
union all select '0014','0008','成都市'

  
  --深度排序（模拟单编码法)
   declare @level_tt table(ybh nvarchar(1000),ebh nvarchar(1000),level int)
  declare @level int
  set @level=0
  insert @level_tt(ybh,ebh,level)
  select ybh,ybh,@level from tb where ebh is null
  while @@ROWCOUNT>0
  begin 
          set @level=@level+1
          insert @level_tt(ybh,ebh,level)
          select a.ybh,b.ebh+a.ybh,@level
            from tb a,@level_tt b
            where a.ebh=b.ybh and b.level=@level-1
 end
select space(b.level*2)+'----'+a.beizhu,a.*,b.*
  from tb a,@level_tt b
  where a.ybh=b.ybh
  order by b.ebh
/*
(结果)    ybh    ebh    beizhu    ybh    ebh    level
----云南省    0001    NULL    云南省    0001    0001    0
  ----昆明市    0002    0001    昆明市    0002    00010002    1
    ----五华区    0005    0002    五华区    0005    000100020005    2
      ----西园路192号    0006    0005    西园路192号    0006    0001000200050006    3
        ----金色梧桐3-702    0010    0006    金色梧桐3-702    0010    00010002000500060010    4
          ----昆明越科时空科技有限公司    0011    0010    昆明越科时空科技有限公司    0011    000100020005000600100011    5
    ----水富县    0007    0002    水富县    0007    000100020007    2
      ----两碗乡    0015    0007    两碗乡    0015    0001000200070015    3
        ----两碗村    0013    0015    两碗村    0013    00010002000700150013    4
          ----某跨国集团董事长    0012    0013    某跨国集团董事长    0012    000100020007001500130012    5
  ----昭通市    0003    0001    昭通市    0003    00010003    1
  ----大理市    0009    0001    大理市    0009    00010009    1
----贵州省    0004    NULL    贵州省    0004    0004    0
----四川省    0008    NULL    四川省    0008    0008    0
  ----成都市    0014    0008    成都市    0014    00080014    1
  */
  
  --查找子节点（包括本身节点和子节点)
 declare @level_tt table(ybh nvarchar(1000),ebh nvarchar(1000),level int)
  declare @level int
  set @level=0
  insert @level_tt(ybh,ebh,level)
  select ybh,ybh,@level from tb where ybh='0005'
  while @@ROWCOUNT>0
  begin 
          set @level=@level+1
          insert @level_tt(ybh,ebh,level)
          select a.ybh,b.ebh+a.ybh,@level
            from tb a,@level_tt b
            where a.ebh=b.ybh and b.level=@level-1
 end
select space(b.level*2)+'----'+a.beizhu,a.*,b.*
  from tb a,@level_tt b
  where a.ybh=b.ybh
  order by b.ebh
/*
(无列名)    ybh    ebh    beizhu    ybh    ebh    level
----五华区    0005    0002    五华区    0005    0005    0
  ----西园路192号    0006    0005    西园路192号    0006    00050006    1
    ----金色梧桐3-702    0010    0006    金色梧桐3-702    0010    000500060010    2
      ----昆明越科时空科技有限公司    0011    0010    昆明越科时空科技有限公司    0011    0005000600100011    3
*/
  

#10

--广度排序   
create table #Info   
(   
  ID char(3),   
  PID char(3),   
  [Name] varchar(20)   
)   
insert into #Info select '001',null,'山东省'   
union all select '002','001','烟台市'   
union all select '004','002','招远市'   
union all select '003','001','青岛市'   
union all select '005',null,'四会市'   
union all select '006','005','清远市'   
union all select '007','006','小分市'   
  
create table #TT   
(   
  ID char(3),   
  [Level] int   
)   
declare @level int   
set @level=0  
insert into #TT select ID,@level from #Info where PID is null   
--select * from #TT   
while @@rowcount>0   
begin   
  set @level=@level+1   
  insert into #TT select I.ID,@level from #Info I,#TT T where I.PID=T.ID and [Level]=@level-1   
end   
  
select I.* from #Info I join #TT T on I.ID=T.ID order by T.[level]   
  
    
  
ID   PID  Name   
---- ---- --------------------   
001  NULL 山东省   
005  NULL 四会市   
002  001  烟台市   
003  001  青岛市   
006  005  清远市   
004  002  招远市   
007  006  小分市   
  
(7 行受影响)   
  
--查找子节点   
  
create table Info   
(   
  ID char(3),   
  PID char(3),   
  [Name] varchar(20)   
)   
insert into Info select '001',null,'山东省'   
union all select '002','001','烟台市'   
union all select '004','002','招远市'   
union all select '003','001','青岛市'   
union all select '005',null,'四会市'   
union all select '006','005','清远市'   
union all select '007','006','小分市'   
  
create function dbo.f_id(@id char(3))   
returns @tb_level table(id char(3),[level] int)   
as   
begin   
 declare @level int   
  set @level=0  
 insert into @tb_level select @id,@level   
 while @@rowcount>0   
 begin   
   set @level=@level+1;   
   insert into @tb_level select I.ID,@level from Info I,@tb_level tb where I.PID=tb.ID and [level]=@level-1   
 end   
return   
end    
  
select I.* from Info I,dbo.f_id('002') F where I.ID=F.ID   
  
ID   PID  Name   
---- ---- --------------------   
002  001  烟台市   
004  002  招远市   
  
(2 行受影响)  


本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ws_hgo/archive/2009/07/24/4377428.aspx

#11

学习。。。。

#12

性能一般
实用性不错
灵活性也挺好

#13

值得学习

#14

引用 6 楼 beirut 的回复:

老大的博客
树形结构

up..........

#15

谢谢各位帮忙，有头绪了。