sql 递归问题

Question

本帖最后由 gaoqingchun2007 于 2011-03-31 08:28:45 编辑

Answer 1

--没办法的办法create table [dbo].[tb]
(
[id] [int] not null,
[parentid] [int] null,
[nodeno] [varchar](50) null,
[nodename] [varchar](50) null,
[islast] [int] null,
)goinsert [tb] ([id],[parentid],[nodeno],[nodename],[islast]) values ( 1,0,'01','系统管理',0)
insert [tb] ([id],[parentid],[nodeno],[nodename],[islast]) values ( 2,1,'0101','基础信息',0)
insert [tb] ([id],[parentid],[nodeno],[nodename],[islast]) values ( 3,2,'010101','人员管理',1)
insert [tb] ([id],[parentid],[nodeno],[nodename],[islast]) values ( 4,1,'0102','系统配置',0)
insert [tb] ([id],[parentid],[nodeno],[nodename],[islast]) values ( 5,4,'010201','菜单管理',1)
insert [tb] ([id],[parentid],[nodeno],[nodename],[islast]) values ( 6,0,'02','消息管理',0)
insert [tb] ([id],[parentid],[nodeno],[nodename],[islast]) values ( 7,6,'0201','消息提醒',1)
insert [tb] ([id],[parentid],[nodeno],[nodename],[islast]) values ( 8,0,'03','操作日志',1)
insert [tb] ([id],[parentid],[nodeno],[nodename],[islast]) values ( 9,2,'010102','部门管理',1)
gocreate function f_getP(@id int) 
returns @re table(id int,[level] int,gid int) 
as 
begin
    declare @l int 
    set @l=0 
    insert @re select @id,@l,@id
    while @@rowcount>0 
    begin 
 set @l=@l+1
 insert @re select a.parentid,@l,@id from tb 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 
gocreate table ta(id int,[level] int,gid int)
godeclare @sql varchar(max)
declare @str varchar(max)
select @sql = isnull(@sql,'') + ' union all select * from dbo.f_getP(' + ltrim(id) + ')'
from(select id from tb where islast = 1)t
set @sql = stuff(@sql,1,10,'')
insert into ta exec(@sql)set @str = 'select a.gid'
select @str = @str + ',max(case a.[level] when ' + ltrim([level]) + ' then b.nodename else null end) [' + ltrim([level]) + '级节点]'
from(select distinct [level] from ta)t
select @str = @str + ' from ta a join tb b on a.id = b.id group by a.gid'
exec(@str)drop function f_getP
drop table tb,ta
/*gid         1级节点                                               2级节点                                               3级节点
----------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
3           系统管理                                               基础信息                                               人员管理
5           系统管理                                               系统配置                                               菜单管理
7           消息管理                                               消息提醒                                               NULL
8           操作日志                                               NULL                                               NULL
9           系统管理                                               基础信息                                               部门管理
警告: 聚合或其他 SET 操作消除了空值。(5 行受影响)

Answer 2

BOM按节点排序应用实例 
----------------------------------------------------------------------------  Author : htl258(Tony)--  Date   : 2010-04-23 02:37:28--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) --          Jul  9 2008 14:43:34 --          Copyright (c) 1988-2008 Microsoft Corporation--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)--  Subject: BOM按节点排序应用实例-------------------------------------------------------------------------- --实例1:--> 生成测试数据表:tb IF NOT OBJECT_ID('[tb]') IS NULL    DROP TABLE [tb]GOCREATE TABLE [tb]([id] INT,[code] NVARCHAR(10),[pid] INT,[name] NVARCHAR(10))INSERT [tb]SELECT 1,'01',0,N'服装' UNION ALLSELECT 2,'01',1,N'男装' UNION ALLSELECT 3,'01',2,N'西装' UNION ALLSELECT 4,'01',3,N'全毛' UNION ALLSELECT 5,'02',3,N'化纤' UNION ALLSELECT 6,'02',2,N'休闲装' UNION ALLSELECT 7,'02',1,N'女装' UNION ALLSELECT 8,'01',7,N'套装' UNION ALLSELECT 9,'02',7,N'职业装' UNION ALLSELECT 10,'03',7,N'休闲装' UNION ALLSELECT 11,'04',7,N'西装' UNION ALLSELECT 12,'01',11,N'全毛' UNION ALLSELECT 13,'02',11,N'化纤' UNION ALLSELECT 14,'05',7,N'休闲装'GO--SELECT * FROM [tb] -->SQL查询如下: ;WITH T AS(    SELECT CAST(CODE AS VARCHAR(20)) AS CODE,*,        CAST(ID AS VARBINARY(MAX)) AS px     FROM tb AS A    WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.pid)    UNION ALL     SELECT CAST(B.CODE+A.CODE AS VARCHAR(20)),A.*,         CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX))        FROM tb AS A        JOIN T AS B           ON A.pid=B.id)SELECT Code,Name FROM T ORDER BY px/*Code                 Name-------------------- ----------01                   服装0101                 男装010101               西装01010101             全毛01010102             化纤010102               休闲装0102                 女装010201               套装010202               职业装010203               休闲装010204               西装01020401             全毛01020402             化纤010205               休闲装 (14 行受影响)*/ --实例2:--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[parentid] INT,[categoryname] NVARCHAR(10))
INSERT [tb]
SELECT 1,0,'test1' UNION ALL
SELECT 2,0,'test2' UNION ALL
SELECT 3,1,'test1.1' UNION ALL
SELECT 4,2,'test2.1' UNION ALL
SELECT 5,3,'test1.1.1' UNION ALL
SELECT 6,1,'test1.2'
GO
--SELECT * FROM [tb]-->SQL查询如下:
;WITH T AS
(
    SELECT *,CAST(ID AS VARBINARY(MAX)) AS px 
    FROM tb AS A
    WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.[parentid])
    UNION ALL 
    SELECT A.*,CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX))  
    FROM tb AS A
        JOIN T AS B
           ON A.[parentid]=B.id
)
SELECT [id],[parentid],[categoryname] FROM T 
ORDER BY px
/*
id          parentid    categoryname
----------- ----------- ------------
1           0           test1
3           1           test1.1
5           3           test1.1.1
6           1           test1.2
2           0           test2
4           2           test2.1(6 行受影响)
*/本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2010/04/23/5518166.aspx

Answer 3

同级节点有多个,如何办?建议整成字符串形式.
参考如下:/*
标题：SQL SERVER 2000中查询指定节点及其所有子节点的函数(字符串形式显示)
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 
时间：2010-02-02
地点：新疆乌鲁木齐
*/--生成测试数据 
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(10)) returns varchar(8000) 
as 
begin 
  declare @i int , @ret varchar(8000) 
  declare @t table(id varchar(10) , pid varchar(10) , level int) 
  set @i = 1 
  insert into @t select id , pid , @i from tb where id = @id 
  while @@rowcount <> 0 
  begin 
    set @i = @i + 1 
    insert into @t select a.id , a.pid , @i from tb a , @t b where a.pid = b.id and b.level = @i - 1
  end 
  select @ret = isnull(@ret , '') + id + ',' from @t 
  return left(@ret , len(@ret) - 1)
end 
go --执行查询 
select id , children = isnull(dbo.f_cid(id) , '') from tb group by iddrop table tb
drop function f_cid/*
id   children                               
---- ---------------------------------------
001  001,002,003,004,005,006,007,008,009,010
002  002,004
003  003,005,006,007,008,009,010
004  004
005  005
006  006
007  007,008,009,010
008  008
009  009
010  010（所影响的行数为 10 行）
*/
/*
标题：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;with t as
(
    select id , cid = id from tb 
    union all
    select t.id , cid = tb.id 
    from t join tb on tb.pid = t.cid 
)
select id , cid = STUFF((SELECT ',' + rtrim(cid) FROM t WHERE id = tb.id FOR XML PATH('')) , 1 , 1 , '')
from tb
group by id
order by id
/*
id   cid
---- ---------------------------------------
001  001,002,003,005,006,007,008,009,010,004
002  002,004
003  003,005,006,007,008,009,010
004  004
005  005
006  006
007  007,008,009,010
008  008
009  009
010  010(10 行受影响)
*/;with t as
(
    select id , name , cid = id , path = cast(name as nvarchar(100)) from tb 
    union all
    select t.id , t.name , cid = tb.id , path = cast(tb.name as nvarchar(100))
    from t join tb on tb.pid = t.cid 
)
select id , name , 
       cid = STUFF((SELECT ',' + rtrim(cid) FROM t WHERE id = tb.id FOR XML PATH('')) , 1 , 1 , ''),
       path = STUFF((SELECT ',' + path FROM t WHERE id = tb.id FOR XML PATH('')) , 1 , 1 , '')
from tb
group by id , name
order by id
/*
id   name       cid                                         path
---- ---------- ------------------------------------------- ---------------------------------------------------------------------
001  广东省     001,002,003,005,006,007,008,009,010,004     广东省,广州市,深圳市,罗湖区,福田区,宝安区,西乡镇,龙华镇,松岗镇,天河区
002  广州市     002,004                                     广州市,天河区
003  深圳市     003,005,006,007,008,009,010                 深圳市,罗湖区,福田区,宝安区,西乡镇,龙华镇,松岗镇
004  天河区     004                                         天河区
005  罗湖区     005                                         罗湖区
006  福田区     006                                         福田区
007  宝安区     007,008,009,010                             宝安区,西乡镇,龙华镇,松岗镇
008  西乡镇     008                                         西乡镇
009  龙华镇     009                                         龙华镇
010  松岗镇     010                                         松岗镇(10 行受影响)
*/drop table tb

Answer 4

create table [dbo].[tb]
(
    [id] [int] not null,
    [parentid] [int] null,
    [nodeno] [varchar](50) null,
    [nodename] [varchar](50) null,
    [islast] [int] null,
)
go
insert [tb] ([id],[parentid],[nodeno],[nodename],[islast]) values ( 1,0,'01','系统管理',0)
insert [tb] ([id],[parentid],[nodeno],[nodename],[islast]) values ( 2,1,'0101','基础信息',0)
insert [tb] ([id],[parentid],[nodeno],[nodename],[islast]) values ( 3,2,'010101','人员管理',1)
insert [tb] ([id],[parentid],[nodeno],[nodename],[islast]) values ( 4,1,'0102','系统配置',0)
insert [tb] ([id],[parentid],[nodeno],[nodename],[islast]) values ( 5,4,'010201','菜单管理',1)
insert [tb] ([id],[parentid],[nodeno],[nodename],[islast]) values ( 6,0,'02','消息管理',0)
insert [tb] ([id],[parentid],[nodeno],[nodename],[islast]) values ( 7,6,'0201','消息提醒',1)
insert [tb] ([id],[parentid],[nodeno],[nodename],[islast]) values ( 8,0,'03','操作日志',1)
insert [tb] ([id],[parentid],[nodeno],[nodename],[islast]) values ( 9,2,'010102','部门管理',1)
go--局限性（由于是通过nodename算出排序，所以必须保证各级别nodename不能有重复）alter proc proc_test
as
begin
if exists (select * from dbo.sysobjects where id = object_id(N'temp') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table temp
begin
;with cte 
as 
(select ID,parentid,nodeno,nodename,islast  from tb  
union all
select tb.ID,tb.parentid,tb.nodeno,cte.nodename,tb.islast
from cte,tb where cte.id=tb.parentid)select a.ID,cast(row_number() over (partition by a.id order by B.id) as varchar)+'级节点' as cid
,a.nodename into temp from cte a,tb b 
where exists
(select len(nodeno),islast from cte where len(cte.nodeno)=len(a.nodeno)) 
and a.islast=1
and a.nodename=b.nodename
enddeclare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + CAST(cid as varchar) from temp group by cid
set @sql = '[' + @sql + ']'exec ('select * from temp pivot (max(nodename) for cid in (' + @sql + ')) b ')
endexec proc_test

Answer 5

exec proc_test
ID          1级节点                                               2级节点                                               3级节点
----------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
3           系统管理                                               基础信息                                               人员管理
5           系统管理                                               系统配置                                               菜单管理
7           消息管理                                               消息提醒                                               NULL
8           操作日志                                               NULL                                               NULL
9           系统管理                                               基础信息                                               部门管理(5 行受影响)

Answer 6

id 一级节点 二级节点 三级节点3 系统管理 基础信息 人员管理
9 系统管理 基础信息 部门管理
7 消息管理 消息提醒  NULL 
5 系统管理 系统配置 菜单管理
8 操作日志  NULL    NULL实现这种格式，最好能写成函数，以便和其他表关联操作

Answer 7

需求又加了个数量列（amount），要按一级节点小计、合计
id 一级节点 二级节点 三级节点 数量3 系统管理 基础信息 人员管理 30
9 系统管理 基础信息 部门管理 20
5 系统管理 系统配置 菜单管理 60
小计 110
7 消息管理 消息提醒 NULL     40
小计 40
8 操作日志 NULL NULL         70
小计 70
合计：220

Answer 8

如果是固定的三级:
CREATE TABLE [dbo].[TB](
[id] [int] IDENTITY(1,1) NOT NULL,
[parentId] [int] NULL,
[nodeNo] [varchar](50) NULL,
[nodeName] [varchar](50) NULL,
[isLast] [int] NULL,
 CONSTRAINT [PK_TB] PRIMARY KEY CLUSTERED  
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [TB] ([parentId],[nodeNo],[nodeName],[isLast]) VALUES ( 0,'01','系统管理',0)
INSERT [TB] ([parentId],[nodeNo],[nodeName],[isLast]) VALUES ( 1,'0101','基础信息',0)
INSERT [TB] ([parentId],[nodeNo],[nodeName],[isLast]) VALUES ( 2,'010101','人员管理',1)
INSERT [TB] ([parentId],[nodeNo],[nodeName],[isLast]) VALUES ( 1,'0102','系统配置',0)
INSERT [TB] ([parentId],[nodeNo],[nodeName],[isLast]) VALUES ( 4,'010201','菜单管理',1)
INSERT [TB] ([parentId],[nodeNo],[nodeName],[isLast]) VALUES ( 0,'02','消息管理',0)
INSERT [TB] ([parentId],[nodeNo],[nodeName],[isLast]) VALUES ( 6,'0201','消息提醒',1)
INSERT [TB] ([parentId],[nodeNo],[nodeName],[isLast]) VALUES ( 0,'03','操作日志',1)
INSERT [TB] ([parentId],[nodeNo],[nodeName],[isLast]) VALUES ( 2,'010102','部门管理',1)
;with c1 as(
select id,nodeName as [一级节点] from tb where parentid=0
),c2 as(
select a.id,b.id sub1id,a.[一级节点],b.nodename as [二级节点] from c1 a left join tb b on a.id=b.parentid
),c3 as(
select a.id,a.sub1id,b.id sub2id,a.[一级节点],a.[二级节点],b.nodename as [三级节点] from c2 a left join tb b on a.sub1id=b.parentid
)select isnull(isnull(sub2id,sub1id),id)id,[一级节点],[二级节点],[三级节点] from c3
go
drop table tb
/*
id          一级节点                                               二级节点                                               三级节点
----------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
3           系统管理                                               基础信息                                               人员管理
9           系统管理                                               基础信息                                               部门管理
5           系统管理                                               系统配置                                               菜单管理
7           消息管理                                               消息提醒                                               NULL
8           操作日志                                               NULL                                               NULL(5 行受影响)*/

Answer 9

本帖最后由 fcuandy 于 2011-04-01 09:26:01 编辑

  

Answer 10

我这里测试可行的啊。。你要么先建立表，分别执行
最后在运行
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + CAST(cid as varchar) from temp group by cid
set @sql = '[' + @sql + ']'exec ('select * from temp pivot (max(nodename) for cid in (' + @sql + ')) b ')