上面的一个├应该是└,所以上面的作废,看这个
请看这里
http://blog.csdn.net/xluzhong/articles/299618.aspx?Pending=true它的执行效果不是我要的,我要shuxing name id
AAAA AAAA 1
├—AAAA-1 AAAA-1 4
│ └—AAAA-1-1 AAAA-1-1 5
├—AAAA-2 AAAA-2 9
BBBB BBBB 2
├—BBBB-1 BBBB-1 6
CCCC CCCC 3
├—CCCC-1 CCCC-1 7
└—CCCC-2 CCCC-2 8
各位高人帮帮忙,我搜了论坛里没有。是否要实现这样的效果要增加一个depth字段
请看这里
http://blog.csdn.net/xluzhong/articles/299618.aspx?Pending=true它的执行效果不是我要的,我要shuxing name id
AAAA AAAA 1
├—AAAA-1 AAAA-1 4
│ └—AAAA-1-1 AAAA-1-1 5
├—AAAA-2 AAAA-2 9
BBBB BBBB 2
├—BBBB-1 BBBB-1 6
CCCC CCCC 3
├—CCCC-1 CCCC-1 7
└—CCCC-2 CCCC-2 8
各位高人帮帮忙,我搜了论坛里没有。是否要实现这样的效果要增加一个depth字段
http://blog.csdn.net/zjcxc/archive/2005/07/27/436328.aspx
在关键字 'WITH' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,过程 f_id,行 14
第 14 行: ')' 附近有语法错误。
而且就是不出错,也实现不了我要的效果吧
谁写个sql 2000的,200分都是他的了。必须实现我要的效果。
分不够可以再开贴。
--树形数据查询示例
--作者: 邹建if exists (select * from dbo.sysobjects where id = object_id(N'[tb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tb]
GO--示例数据
create table [tb]([id] int identity(1,1),[pid] int,name varchar(20))
insert [tb] select 0,'中国'
union all select 0,'美国'
union all select 0,'加拿大'
union all select 1,'北京'
union all select 1,'上海'
union all select 1,'江苏'
union all select 6,'苏州'
union all select 7,'常熟'
union all select 6,'南京'
union all select 6,'无锡'
union all select 2,'纽约'
union all select 2,'旧金山'
goif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_id]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_id]
GO/*--树形数据处理 级别及排序字段--邹建 2003-12(引用请保留此信息)--*//*--调用示例 --调用函数实现分级显示
select replicate('-',b.[level]*4)+a.name
from [tb] a,f_id()b
where a.[id]=b.[id]
order by b.sid
--*/
create function f_id()
returns @re table([id] int,[level] int,sid varchar(8000))
as
begin
declare @l int
set @l=0
insert @re select [id],@l,right(10000+[id],4)
from [tb] where [pid]=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.[id],@l,b.sid+right(10000+a.[id],4)
from [tb] a,@re b
where a.[pid]=b.[id] and b.[level]=@l-1
end
return
end
go--调用函数实现分级显示
select replicate('-',b.[level]*4)+a.name
from [tb] a,f_id()b
where a.[id]=b.[id]
order by b.sid
go
--删除测试
drop table [tb]
drop function f_id
go/*--结果
中国
----北京
----上海
----江苏
--------苏州
------------常熟
--------南京
--------无锡
美国
----纽约
----旧金山
加拿大--*/
----------- ---------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 AAAA 0 AAAA
4 AAAA-1 1 └AAAA-1
9 AAAA-1-1 4 └└AAAA-1-1
5 AAAA-2 1 └AAAA-2
2 BBBB 0 BBBB
6 BBBB-1 2 └BBBB-1
3 CCCC 0 CCCC
7 CCCC-1 3 └CCCC-1
8 CCCC-2 3 └CCCC-2(所影响的行数为 9 行)--这种效果可以吗?
create table tb(ID int,Name varchar(10),ParentID int)
insert tb select 1,'AAAA' ,0
union all select 2,'BBBB' ,0
union all select 3,'CCCC' ,0
union all select 4,'AAAA-1' ,1
union all select 5,'AAAA-2' ,1
union all select 6,'BBBB-1' ,2
union all select 7,'CCCC-1' ,3
union all select 8,'CCCC-2' ,3
union all select 9,'AAAA-1-1',4
go--创建处理的函数
create function f_id()
returns @re table(id int,level int,sid varchar(8000))
as
begin
declare @l int
set @l=0
insert @re select id,@l,right(10000+id,4)
from tb where ParentID=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.id,@l,b.sid+','+right(10000+a.id,4)
from tb a,@re b
where a.ParentID=b.id and b.level=@l-1
end
return
end
go--调用函数实现查询
select 带缩进的Name=
(case when b.level=0 then a.Name
when b.level=1 then '├'+a.Name
when b.level=2 then ' └'+a.Name
when b.level=3 then ' └'+a.name
end),a.name,a.id
from tb a,f_id() b
where a.id=b.id
order by b.sid
go
--删除测试
drop table tb
drop function f_id/*(所影响的行数为 9 行)带缩进的Name name id
------------------- ---------- -----------
AAAA AAAA 1
├AAAA-1 AAAA-1 4
└AAAA-1-1 AAAA-1-1 9
├AAAA-2 AAAA-2 5
BBBB BBBB 2
├BBBB-1 BBBB-1 6
CCCC CCCC 3
├CCCC-1 CCCC-1 7
├CCCC-2 CCCC-2 8(所影响的行数为 9 行)
*/
是你要的效果吗??
when b.level=1 then '├'+a.Name
when b.level=2 then ' └'+a.Name
when b.level=3 then ' └'+a.name这明显在对付啊。
when b.level=1 then '├'+a.Name
when b.level=2 then ' └'+a.Name
when b.level=3 then ' └'+a.name这明显在对付啊。
--楼主,这也是根据树的层的结构来判断的啊,你说的"还有最后一行,应该用└,而不是├"
这个不知道怎么搞.
再你提供的上面更动的。
*/
create table tb(ID int,Name varchar(10),ParentID int)
insert tb select 1,'AAAA' ,0
union all select 2,'BBBB' ,0
union all select 3,'CCCC' ,0
union all select 4,'AAAA-1' ,1
union all select 5,'AAAA-2' ,1
union all select 6,'BBBB-1' ,2
union all select 7,'CCCC-1' ,3
union all select 8,'CCCC-2' ,3
union all select 9,'AAAA-1-1',4
go--创建处理的函数
create function f_id()
returns @re table(id int,level int,sid varchar(8000))
as
begin
declare @l int
set @l=0
insert @re select id,@l,right(10000+id,4)
from tb where ParentID=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.id,@l,b.sid+','+right(10000+a.id,4)
from tb a,@re b
where a.ParentID=b.id and b.level=@l-1
end
return
end
go
case parentid when 0 then '' else '|' end +
case when parentid<3 then replicate('-',convert(varchar,(b.level)*4))
else replicate(' ',convert(varchar,(b.level)*2))+'|'+replicate('-',convert(varchar,(b.level)*2))
end +a.name
--调用函数实现查询
select a.*,带缩进的Name=case parentid when 0 then '' else '|' end +
case when parentid<3 then replicate('-',convert(varchar,(b.level)*4))
else replicate(' ',convert(varchar,(b.level)*2))+'|'+replicate('-',convert(varchar,(b.level)*2))
end +a.name
from tb a,f_id() b
where a.id=b.id
order by b.sid
go/*
结果
id name parentid 带缩进的Name
----------------------------------------
1 AAAA 0 AAAA
4 AAAA-1 1 |----AAAA-1
9 AAAA-1-1 4 | |----AAAA-1-1
5 AAAA-2 1 |----AAAA-2
2 BBBB 0 BBBB
6 BBBB-1 2 |----BBBB-1
3 CCCC 0 CCCC
7 CCCC-1 3 | |--CCCC-1
8 CCCC-2 3 | |--CCCC-2*/
--删除测试
drop table tb
drop function f_id
create table tb(ID int,Name varchar(10),ParentID int)
insert tb select 1,'AAAA' ,0
union all select 2,'BBBB' ,0
union all select 3,'CCCC' ,0
union all select 4,'AAAA-1' ,1
union all select 5,'AAAA-2' ,1
union all select 6,'BBBB-1' ,2
union all select 7,'CCCC-1' ,3
union all select 8,'CCCC-2' ,3
union all select 9,'AAAA-1-1',4
go--创建处理的函数
create function f_id()
returns @re table(id int,level int,sid varchar(8000))
as
begin
declare @l int
set @l=0
insert @re select id,@l,right(10000+id,4)
from tb where ParentID=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.id,@l,b.sid+','+right(10000+a.id,4)
from tb a,@re b
where a.ParentID=b.id and b.level=@l-1
end
return
end
go--调用函数实现查询
select a.*,带缩进的Name=case parentid when 0 then '' else '|' end +
case when parentid<3 then replicate('-',convert(varchar,(b.level)*4))
else replicate(' ',convert(varchar,(b.level)*2)) end +
case parentid when (select max(parentid) from tb) then '└' else '' end+
case when parentid<3 then '' else replicate('-',convert(varchar,(b.level)*2)) end
+a.name
from tb a,f_id() b
where a.id=b.id
order by b.sid
go/*
结果
id name parentid 带缩进的Name
----------------------------------------
1 AAAA 0 AAAA
4 AAAA-1 1 |----AAAA-1
9 AAAA-1-1 4 | └----AAAA-1-1
5 AAAA-2 1 |----AAAA-2
2 BBBB 0 BBBB
6 BBBB-1 2 |----BBBB-1
3 CCCC 0 CCCC
7 CCCC-1 3 | --CCCC-1
8 CCCC-2 3 | --CCCC-2*/
--删除测试
drop table tb
drop function f_id
select a.*,带缩进的Name=case parentid when 0 then '' else '|' end +
case when b.level<3 then replicate('-',convert(varchar,(b.level)*4))
else replicate(' ',convert(varchar,(b.level)*2)) end +
case parentid when (select max(parentid) from tb) then '└' else '' end+
case when b.level<3 then '' else replicate('-',convert(varchar,(b.level)*2)) end
+a.name
from tb a,f_id() b
where a.id=b.id
order by b.sid
go
--调用函数实现查询
select a.*,带缩进的Name=case parentid when 0 then '' else '|' end +
case when b.level<2 then replicate('-',convert(varchar,(b.level)*4))
else replicate(' ',convert(varchar,(b.level)*2))+'└'+
replicate('-',convert(varchar,(b.level)*2)) end
+a.name
from tb a,f_id() b
where a.id=b.id
order by b.sid
go/*
结果
id name parentid 带缩进的Name
----------------------------------------
1 AAAA 0 AAAA
4 AAAA-1 1 |----AAAA-1
9 AAAA-1-1 4 | └----AAAA-1-1
5 AAAA-2 1 |----AAAA-2
2 BBBB 0 BBBB
6 BBBB-1 2 |----BBBB-1
3 CCCC 0 CCCC
7 CCCC-1 3 |----CCCC-1
8 CCCC-2 3 |----CCCC-2*/
drop table [tb]
GO
--示例数据
create table tb(ID int,Name varchar(10),ParentID int)
insert tb select 1,'AAAA' ,0
union all select 2,'BBBB' ,0
union all select 3,'CCCC' ,0
union all select 4,'AAAA-1' ,1
union all select 5,'AAAA-2' ,1
union all select 6,'BBBB-1' ,2
union all select 7,'CCCC-1' ,3
union all select 8,'CCCC-2' ,3
union all select 9,'AAAA-1-1',4
union all select 10,'CCCC-3' ,8
goif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_id]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_id]
GO
--创建处理的函数
create function f_id()
returns @re table(id int,level int,sid varchar(8000))
as
begin
declare @l int
set @l=0
insert @re select id,@l,right(10000+id,4)
from tb where ParentID=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.id,@l,b.sid+','+right(10000+a.id,4)
from tb a,@re b
where a.ParentID=b.id and b.level=@l-1
end
return
end
go--调用函数实现查询
select 带缩进的Name=
(case when b.level=0 then a.Name
when b.level=1 and a.id<>8 then '├'+a.Name
when a.id=9 then '│└'+a.Name
when a.id=8 then '└'+a.Name
when a.id=10 then ' └'+a.Name
when b.level=3 then ' └'+a.name
end),a.name,a.id
from tb a,f_id() b
where a.id=b.id
order by b.sid
go
--删除测试
drop table tb
drop function f_id
create table tb(ID int,Name varchar(10),ParentID int)
insert tb select 1,'AAAA' ,0
union all select 2,'BBBB' ,0
union all select 3,'CCCC' ,0
union all select 4,'AAAA-1' ,1
union all select 5,'AAAA-2' ,1
union all select 6,'BBBB-1' ,2
union all select 7,'CCCC-1' ,3
union all select 8,'CCCC-2' ,3
union all select 9,'AAAA-1-1',4
go--创建处理的函数(对原函数做了修改,增加了前缀)
create function f_id()
returns @re table(id int,parentid int,level int,sid varchar(8000),pre1 varchar(2),pre2 varchar(2))
as
begin
declare @l int
set @l=0
insert @re select id,parentid,@l,right(10000+id,4),'',''
from tb where ParentID=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.id,a.parentid,@l,b.sid+','+right(10000+a.id,4),'',''
from tb a,@re b
where a.ParentID=b.id and b.level=@l-1
end
update a
set pre2= case id when (select max(id) from @re where level=a.level) then '└' else '│' end
from @re a
where a.level>0 update a
set pre1=case id when (select max(id) from @re where level=a.level-1) then '└' else '│' end
from @re a
where level>1
return
end
goselect 带缩进的Name=case when b.[level]=0 then a.name
when b.[level]=1 then pre2+replicate('-',4)+a.name
else b.pre1+replicate(' ',(b.level-1)*4)+b.pre2+replicate('-',4)+a.name
end,a.name,a.id
from tb a,f_id() b
where a.id=b.id
order by b.sid
/*
结果
id name parentid 带缩进的Name
----------------------------------------
1 AAAA 0 AAAA
4 AAAA-1 1 │----AAAA-1
9 AAAA-1-1 4 │ └----AAAA-1-1
5 AAAA-2 1 │----AAAA-2
2 BBBB 0 BBBB
6 BBBB-1 2 │----BBBB-1
3 CCCC 0 CCCC
7 CCCC-1 3 │----CCCC-1
8 CCCC-2 3 └----CCCC-2
*/
--删除测试
drop table tb
drop function f_id
create table tb(ID int,Name varchar(10),ParentID int)
insert tb select 1,'AAAA' ,0
union all select 2,'BBBB' ,0
union all select 3,'CCCC' ,0
union all select 4,'AAAA-1' ,1
union all select 5,'AAAA-2' ,1
union all select 6,'BBBB-1' ,2
union all select 7,'CCCC-1' ,3
union all select 8,'CCCC-2' ,3
union all select 9,'AAAA-1-1',4
union all select 10,'CCCC-2-1',8
create table tb(ID int,Name varchar(10),ParentID int)
insert tb select 1,'AAAA' ,0
union all select 2,'BBBB' ,0
union all select 3,'CCCC' ,0
union all select 4,'AAAA-1' ,1
union all select 5,'AAAA-2' ,1
union all select 6,'BBBB-1' ,2
union all select 7,'CCCC-1' ,3
union all select 8,'CCCC-2' ,3
union all select 9,'AAAA-1-1',4
union all select 10,'CCCC-2-1',8
union all select 11,'cccc-2-2',8
union all select 12,'AAAA-1-2',4
--处理了最大值id的问题。
create function f_id()
returns @re table(id int,parentid int,level int,sid varchar(8000),pre1 varchar(2),pre2 varchar(2))
as
begin
declare @l int
set @l=0
insert @re select id,parentid,@l,right(10000+id,4),'',''
from tb where ParentID=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.id,a.parentid,@l,b.sid+','+right(10000+a.id,4),'',''
from tb a,@re b
where a.ParentID=b.id and b.level=@l-1
end
update a
set pre2= case id when (select convert(int,right(max(sid),4)) from @re where level=a.level) then '└' else '├' end
from @re a
where a.level>0
update a
set pre1=case when parentid=(select convert(int,right(max(sid),4)) from @re where level=a.level-1) then ' '
else '│' end
from @re a
where level>1
return
endselect 带缩进的Name=case when b.[level]=0 then a.name
when b.[level]=1 then pre2+replicate('-',4)+a.name
else b.pre1+replicate(' ',(b.level-1)*4)+b.pre2+replicate('-',4)+a.name
end,a.name,a.id
from tb a,f_id() b
where a.id=b.id
order by b.sid
/*
带缩进的Name name id
----------------------------------------结果
AAAA AAAA 1
├----AAAA-1 AAAA-1 4
│ ├----AAAA-1-1 AAAA-1-1 9
│ ├----AAAA-1-2 AAAA-1-2 12
├----AAAA-2 AAAA-2 5
BBBB BBBB 2
├----BBBB-1 BBBB-1 6
CCCC CCCC 3
├----CCCC-1 CCCC-1 7
└----CCCC-2 CCCC-2 8
├----CCCC-2-1 CCCC-2-1 10
└----cccc-2-2 cccc-2-2 11*/
│ ├----AAAA-1-2改成
│ └----AAAA-1-2,
马上结贴,分都给你.
create table tb(ID int,Name varchar(10),ParentID int)
insert tb select 1,'AAAA' ,0
union all select 2,'BBBB' ,0
union all select 3,'CCCC' ,0
union all select 4,'AAAA-1' ,1
union all select 5,'AAAA-2' ,1
union all select 6,'BBBB-1' ,2
union all select 7,'CCCC-1' ,3
union all select 8,'CCCC-2' ,3
union all select 9,'AAAA-1-1',4
union all select 10,'CCCC-2-1',8
union all select 11,'cccc-2-2',8
union all select 12,'AAAA-1-2',4
go--创建处理的函数(对原函数做了修改,增加了前缀)
--处理了最大值id的问题。
create function f_id()
returns @re table(id int,parentid int,level int,sid varchar(8000),pre1 varchar(2),pre2 varchar(2))
as
begin
declare @l int
set @l=0
insert @re select id,parentid,@l,right(10000+id,4),'',''
from tb where ParentID=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.id,a.parentid,@l,b.sid+','+right(10000+a.id,4),'',''
from tb a,@re b
where a.ParentID=b.id and b.level=@l-1
end
update a
set pre2= case when a.level>1 then
case id when (select convert(int,right(max(sid),4)) from @re where parentid=a.parentid and level=a.level) then '└' else '├' end
else
case id when (select convert(int,right(max(sid),4)) from @re where level=a.level) then '└' else '├' end
end
from @re a
where a.level>0
update a
set pre1=case when parentid=(select convert(int,right(max(sid),4)) from @re where level=a.level-1) then ' '
else '│' end
from @re a
where level>1
return
endselect 带缩进的Name=case when b.[level]=0 then a.name
when b.[level]=1 then pre2+replicate('-',4)+a.name
else b.pre1+replicate(' ',(b.level-1)*4)+b.pre2+replicate('-',4)+a.name
end,a.name,a.id
from tb a,f_id() b
where a.id=b.id
order by b.sid
/*
结果
带缩进的Name name id
----------------------------------------
AAAA AAAA 1
├----AAAA-1 AAAA-1 4
│ ├----AAAA-1-1 AAAA-1-1 9
│ └----AAAA-1-2 AAAA-1-2 12
├----AAAA-2 AAAA-2 5
BBBB BBBB 2
├----BBBB-1 BBBB-1 6
CCCC CCCC 3
├----CCCC-1 CCCC-1 7
└----CCCC-2 CCCC-2 8
├----CCCC-2-1 CCCC-2-1 10
└----cccc-2-2 cccc-2-2 11*/
--删除测试
drop table tb
drop function f_id
如下:
create table tb(ID int,Name varchar(10),ParentID int)
insert tb select 1,'AAAA' ,0
union all select 2,'BBBB' ,0
union all select 3,'CCCC' ,0
union all select 4,'AAAA-1' ,1
union all select 5,'AAAA-2' ,1
union all select 6,'BBBB-1' ,2
union all select 7,'CCCC-1' ,3
union all select 8,'CCCC-2' ,3
union all select 9,'AAAA-1-1',4
union all select 10,'CCCC-2-1',8
union all select 11,'cccc-2-2',8
union all select 12,'AAAA-1-2',4
union all select 13,'AAAA-1-2',12
union all select 14,'AAAA-1-2',13
go第6行左边的│没了
(总觉得有简单的方法,但每次都匆匆提交)
create function f_id()
returns @re table(id int,parentid int,level int,sid varchar(8000),pre1 varchar(2),pre2 varchar(2))
as
begin
declare @l int
set @l=0
insert @re select id,parentid,@l,right(10000+id,4),'',''
from tb where ParentID=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.id,a.parentid,@l,b.sid+','+right(10000+a.id,4),'',''
from tb a,@re b
where a.ParentID=b.id and b.level=@l-1
end
update a
set pre2= case when a.level>1 then
case id when (select convert(int,right(max(sid),4)) from @re where parentid=a.parentid and level=a.level) then '└' else '├' end
else
case id when (select convert(int,right(max(sid),4)) from @re where level=a.level) then '└' else '├' end
end
from @re a
where a.level>0
update a
set pre1=case when
left(sid,4)=(select convert(int,left(max(sid),4)) from @re where level=0) or
left(sid,4)=(select convert(int,right(max(sid),4)) from @re where level=1)
then ' '
else '│' end
from @re a
where level>1
return
end/*
结果
带缩进的Name name id
----------------------------------------
AAAA AAAA 1
├----AAAA-1 AAAA-1 4
│ ├----AAAA-1-1 AAAA-1-1 9
│ └----AAAA-1-2 AAAA-1-2 12
│ └----AAAA-1-2 AAAA-1-2 13
│ └----AAAA-1-2 AAAA-1-2 14
├----AAAA-2 AAAA-2 5
BBBB BBBB 2
├----BBBB-1 BBBB-1 6
CCCC CCCC 3
├----CCCC-1 CCCC-1 7
└----CCCC-2 CCCC-2 8
├----CCCC-2-1 CCCC-2-1 10
└----cccc-2-2 cccc-2-2 11
*/