create table test
(
id nvarchar(10),
name nvarchar(20),
code nvarchar(100))
insert into test select '01','a1' ,'1817A0Y003.1'
insert into test select '02','a2' ,'1817A0Y003.2.1'
insert into test select '03','a9' ,'1817A0Y003.2.2'
insert into test select '07','a15' ,'1817A0Y003.3'
insert into test select '05','a18' ,'1817A0Y003.4.1.2.1'
insert into test select '04','a8' ,'1817A0Y003.4.1.2.2'
insert into test select '010','a55' ,'1817A0Y003.4.1'
insert into test select '012','a25' ,'1817A0Y003.4.3.1'
insert into test select '017','a14' ,'1817A0Y003.4.5'
insert into test select '027','a24' ,'1817A0Y003.4.4'
insert into test select '0107','a104' ,'1817A0Y003.14.5'
insert into test select '0207','a204' ,'1817A0Y003.14.4'
/*
要得到这样的报表：
id name code
01 a1 1817A0Y003.1
02 a2 1817A0Y003.2.1
03 a9 1817A0Y003.2.2
07 a15 1817A0Y003.3
010 a55 1817A0Y003.4.1
05 a18 1817A0Y003.4.1.2.1
04 a8 1817A0Y003.4.1.2.2
012 a25 1817A0Y003.4.3.1
027 a24 1817A0Y003.4.4
017 a14 1817A0Y003.4.5
0207 a204 1817A0Y003.14.4
0107 a104 1817A0Y003.14.5
*/
code的可能格式：****.数字.数字.数字.数字…… （****长度不定，但一定相同。【.数字】循环次数不定，像树一样）
MS SQLServer 2000
标题：查询各节点的父路径函数
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-05-12
地点：广东深圳
*//*
原始数据及要求结果如下：
--食品
--水果
--香蕉
--苹果
--蔬菜
--青菜
id pid name
----------- ----------- --------------------
1 0 食品
2 1 水果
3 1 蔬菜
4 2 香蕉
5 2 苹果
6 3 青菜要求得到各节点的父路径即如下结果：
id pid name 路径
--- --- ----- ---------------
1 0 食品 食品
2 1 水果 食品,水果
3 1 蔬菜 食品,蔬菜
4 2 香蕉 食品,水果,香蕉
5 2 苹果 食品,水果,苹果
6 3 青菜 食品,蔬菜,青菜
*/create table tb (id int , pid int , name nvarchar(20))
insert into tb values(1 , 0 , '食品')
insert into tb values(2 , 1 , '水果')
insert into tb values(3 , 1 , '蔬菜')
insert into tb values(4 , 2 , '香蕉')
insert into tb values(5 , 2 , '苹果')
insert into tb values(6 , 3 , '青菜')
go--查询各节点的父路径函数
create function f_pid(@id int) returns varchar(100)
as
begin
declare @re_str as varchar(100)
set @re_str = ''
select @re_str = name from tb where id = @id
while exists (select 1 from tb where id = @id and pid <> 0)
begin
select @id = b.id , @re_str = b.name + ',' + @re_str from tb a , tb b where a.id = @id and a.pid = b.id
end
return @re_str
end
goselect * , dbo.f_pid(id) 路径 from tb order by iddrop table tb
drop function f_pidSQL code
/*
标题：查询所有节点及其所有子节点的函数
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)
时间：2009-04-12
地点：广东深圳
*/--生成测试数据
create table tb(id varchar(10),pid varchar(10))
insert into tb select 'a', null
insert into tb select 'b', 'a'
insert into tb select 'c', 'a'
insert into tb select 'd', 'b'
insert into tb select 'e', 'b'
insert into tb select 'f', 'c'
insert into tb select 'g', 'c'
go --创建用户定义函数
create function f_getchild(@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_getchild(id) , '') from tb group by id
go --输出结果
/*
id children
---------- -------------
a a,b,c,d,e,f,g
b b,d,e
c c,f,g
d d
e e
f f
g g（所影响的行数为 7 行）*/ --删除测试数据
drop function f_getchild
drop table tb
create table test
(
id nvarchar(10),
name nvarchar(20),
code nvarchar(100) )
insert into test select '01','a1' ,'1817A0Y003.1'
insert into test select '02','a2' ,'1817A0Y003.2.1'
insert into test select '03','a9' ,'1817A0Y003.2.2'
insert into test select '07','a15' ,'1817A0Y003.3'
insert into test select '05','a18' ,'1817A0Y003.4.1.2.1'
insert into test select '04','a8' ,'1817A0Y003.4.1.2.2'
insert into test select '010','a55' ,'1817A0Y003.4.1'
insert into test select '012','a25' ,'1817A0Y003.4.3.1'
insert into test select '017','a14' ,'1817A0Y003.4.5'
insert into test select '027','a24' ,'1817A0Y003.4.4'
insert into test select '0107','a104' ,'1817A0Y003.14.5'
insert into test select '0207','a204' ,'1817A0Y003.14.4'
--函数
create function dbo.getcode(@code varchar(8000))
returns varchar(8000)
as
begin
declare @str varchar(8000)
select @str=isnull(@str+'.','')+right('0000'+code,4)
from
(select number,code=substring(@code,number,charindex('.',@code+'.',number)- number)
from master..spt_values
where type='p'
and substring('.'+@code,number,1)='.') t
where number <> 1
return @str
end--查询
select * from test
order by dbo.getcode(code)
--结果
-----------------------------------------
01 a1 1817A0Y003.1
02 a2 1817A0Y003.2.1
03 a9 1817A0Y003.2.2
07 a15 1817A0Y003.3
010 a55 1817A0Y003.4.1
05 a18 1817A0Y003.4.1.2.1
04 a8 1817A0Y003.4.1.2.2
012 a25 1817A0Y003.4.3.1
027 a24 1817A0Y003.4.4
017 a14 1817A0Y003.4.5
0207 a204 1817A0Y003.14.4
0107 a104 1817A0Y003.14.5
drop table testcreate table test
(
id nvarchar(10),
name nvarchar(20),
code nvarchar(100) )
insert into test select '01','a1' ,'1817A0Y003.1'
insert into test select '02','a2' ,'1817A0Y003.2.1'
insert into test select '03','a9' ,'1817A0Y003.2.2'
insert into test select '07','a15' ,'1817A0Y003.3'
insert into test select '05','a18' ,'1817A0Y003.4.1.2.1'
insert into test select '04','a8' ,'1817A0Y003.4.1.2.2'
insert into test select '010','a55' ,'1817A0Y003.4.1'
insert into test select '012','a25' ,'1817A0Y003.4.3.1'
insert into test select '017','a14' ,'1817A0Y003.4.5'
insert into test select '027','a24' ,'1817A0Y003.4.4'
insert into test select '0107','a104' ,'1817A0Y003.14.5'
insert into test select '0207','a204' ,'1817A0Y003.14.4'
select * from testdeclare @i int,@max int
declare @a varchar(10)
declare @sql varchar(100)
declare @sql2 varchar(100)
set @sql=''
set @sql2=''
set @i=1
select @max=max(dbo.Spit(code,'.')) from test
while @i<=@max
begin
select @a='a'+cast(@i as varchar(10))
set @sql='alter table test add '+ @a +' int'
set @sql2='update test set '+ @a +'=0'
exec(@sql)
exec(@sql2)
set @sql=''
set @i=@i+1
end
declare @a varchar(100)
declare cur_test cursor for select code from test
open cur_test
fetch next from cur_test into @a
while(@@fetch_status=0)
begin
declare @i int,@max int
declare @b varchar(10)
declare @value varchar(10)
select @max=max(dbo.Spit(code,'.')) from test where code=@a
set @i=1
declare @sql varchar(8000)
select @value=''''+substring(code,charindex('.',code)+1,len(code)-(charindex('.',code))+2)+'''' from test where code=@a
while @i<=@max
begin
set @sql=''
select @b='a'+cast(@i as varchar(10))
--set @sql='update test set '+ @b +'=left('+@value+',charindex(''.'','+@value+')-1) where a='+@a
if charindex('.',@value)>0
begin
set @sql='update test set '+ @b +'=left('+ @value + ',charindex(''.'','+ @value +')-1) where code='''+ @a + ''''
end
else
begin
set @sql='update test set '+ @b +'='+ @value +' where code='''+ @a + ''''
end
exec(@sql)
set @i=@i+1
select @value=''''+substring(@value,charindex('.',@value)+1,len(@value)-charindex('.',@value))
end
fetch next from cur_test into @a
end
close cur_test
deallocate cur_testselect id,name,code from test order by a1,a2,a3,a4-----------------------01 a1 1817A0Y003.1
02 a2 1817A0Y003.2.1
03 a9 1817A0Y003.2.2
07 a15 1817A0Y003.3
010 a55 1817A0Y003.4.1
05 a18 1817A0Y003.4.1.2.1
04 a8 1817A0Y003.4.1.2.2
012 a25 1817A0Y003.4.3.1
027 a24 1817A0Y003.4.4
017 a14 1817A0Y003.4.5
0207 a204 1817A0Y003.14.4
0107 a104 1817A0Y003.14.5
select * from test
order by
(
select right('0000'+code,4) as [text()] from (
select number,code=substring(code,number,charindex('.',code+'.',number)- number)
from master..spt_values
where type='p'
and substring('.'+code,number,1)='.' and number<>1) t for xml path('')
)