一个查询语句排序问题，希望大家指导指导。
在我的数据表中有一个字段 他的纪录值是 文件路径的格式。比如某条纪录的文件路径（surpath）的值 'P:\06\xx\1\1\18\00001.doc '，他可能有某几层的路径是按整数来编写的。像这种 order by surpath 按字符串来排序结果肯定是乱得。
所以现在的问题是如何排序使得查询结果中，路经是连续的（即 1后面是2，而非10）。
为此我先写了个递归函数返回第n层目录的起始位置（惭愧，不知道sql中有无现成的方法。）
CREATE Function dbo.fnGet_nth_DirIndex(@str varchar(255),@index int)
RETURNS INT
As
--取第 n 层目录的起点
Begin
Declare @ret int
if @index > 1
Set @ret =charindex ( '\ ',@str,dbo.fnGet_nth_DirIndex(@str,@index-1)+1)
else
Set @ret =charindex ( '\ ',@str,0)
return @ret
END
调试该函数时
declare @aa varchar(255),@start int
--set @start = 3 --第n级目录
set @aa = 'P:\06\zz\1\1\18\00001.doc '
--返回第3层目录前的字符串
print substring(@aa,0,dbo.fnGet_nth_DirIndex(@aa,3)+1)
--返回第3层目录
print substring(@aa,dbo.fnGet_nth_DirIndex(@aa,3)+1,dbo.fnGet_nth_DirIndex(@aa,3+1)-dbo.fnGet_nth_DirIndex(@aa,3)-1)
--返回第4层目录
print substring(@aa,dbo.fnGet_nth_DirIndex(@aa,4)+1,dbo.fnGet_nth_DirIndex(@aa,4+1)-dbo.fnGet_nth_DirIndex(@aa,4)-1)
--返回第5层目录
print substring(@aa,dbo.fnGet_nth_DirIndex(@aa,5)+1,dbo.fnGet_nth_DirIndex(@aa,5+1)-dbo.fnGet_nth_DirIndex(@aa,5)-1)
输出：
P:\06\zz\
1
1
18
在实际使用时，打算使用下面方法
select id,surpath,edocno from HAM1B4PUS order by
substring(surpath,0,dbo.fnGet_nth_DirIndex(surpath,3)+1),
cast (substring(surpath,dbo.fnGet_nth_DirIndex(surpath,3)+1,dbo.fnGet_nth_DirIndex(surpath,3+1)-dbo.fnGet_nth_DirIndex(surpath,3)-1) as int),
--如果只有上面部分，运行可以通过
--加入下面任何一行都会出错
cast (substring(surpath,dbo.fnGet_nth_DirIndex(surpath,4)+1,dbo.fnGet_nth_DirIndex(surpath,4+1)-dbo.fnGet_nth_DirIndex(surpath,4)-1) as int),
cast (substring(surpath,dbo.fnGet_nth_DirIndex(surpath,5)+1,dbo.fnGet_nth_DirIndex(surpath,5+1)-dbo.fnGet_nth_DirIndex(surpath,5)-1) as int)
用上面的方法也是被逼无奈，在效率上是无法挽回了，
不知大家有没有更好的方法？或我使用的这语句为什么出错，如何改正？
[解决办法]
是因为有些目录的级数不足4级或者5级，这样你的函数返回的是负值，使substring函数出错
[解决办法]
select top 1 surpath from table order by len(surpath) desc
--按 '\ '拆分
CREATE TABLE A
(
col1 varchar(10),--主键列
col2 varchar(50)
)
INSERT INTO A
SELECT 'P:\06\xx\1\1\18\00001.doc 'UNION ALL
SELECT 'P:\06\xx\1\1\00001.doc 'UNION ALL
SELECT 'P:\06\xx\2\1\18\00001.doc 'UNION ALL
SELECT 'P:\06\xx\2\1\00001.doc '
GO
DECLARE @i int,@s VARCHAR(1000)
SET @i=0
SELECT col1,col2 INTO #t FROM A
WHILE @@ROWCOUNT> 0
BEGIN
SELECT @i=@i+1,@s= 'ALTER TABLE #t ADD col2 '+CAST(@i as varchar)+ ' VARCHAR(10) '
EXEC(@s)
SET @s= ' UPDATE #t SET col2 '+CAST(@i as varchar)+ ' =LEFT(col2,CHARINDEX( ' '\ ' ',col2+ ' '\ ' ')-1),col2=STUFF(col2,1,CHARINDEX( ' '\ ' ',col2+ ' ', ' '), ' ' ' ') WHERE col2 > ' ' ' ' '
EXEC(@s)
END
SET @s= 'ALTER TABLE #t DROP COLUMN col2,col2 '+CAST(@i AS VARCHAR)
EXEC(@s)
declare @j int
set @j=1
set @s= 'SELECT aa.* FROM tb aa inner join #t bb on aa.col1=bb.col1 order by '
while @j <@i
begin
set @s=@s+ 'bb.col2 '+CAST(@j AS VARCHAR)+ ', '
set @j=@j+1
end
set @s=left(@s,len(@s)-1)
exec(@s)
DROP TABLE #t
DROP TABLE A
[解决办法]
create function f_test(@code varchar(100))
returns varchar(100)
as
begin
declare @n_code varchar(100)
while charindex( '\ ',@code)> 0
begin
set @n_code=isnull(@n_code, ' ')+
case isnumeric(left(@code,charindex( '\ ',@code)-1))
when 1 then right( '0000000 '+left(@code,charindex( '\ ',@code)-1),8)
else left(@code,charindex( '\ ',@code)-1)
end+ '\ '
set @code=stuff(@code,1,charindex( '\ ',@code), ' ')
end
set @n_code=@n_code+@code
return @n_code
end
go
declare @t table(path varchar(40))
insert into @t select 'P:\06\xx\1\1\18\00001.doc '
insert into @t select 'P:\06\xx\1\2\18\00001.doc '
insert into @t select 'P:\06\xx\1\11\2\00001.doc '
insert into @t select 'P:\06\xx\1\11\18\00001.doc '
insert into @t select 'P:\06\xx\1\12\18\00001.doc '
select * from @t order by dbo.f_test(path)
/*
path
----------------------------------------
P:\06\xx\1\1\18\00001.doc
P:\06\xx\1\2\18\00001.doc
P:\06\xx\1\11\2\00001.doc
P:\06\xx\1\11\18\00001.doc
P:\06\xx\1\12\18\00001.doc
*/
go
drop function f_test
