一个查询语句排序问题，希望大家指导指导。

Question

在我的数据表中有一个字段 他的纪录值是 文件路径的格式。比如某条纪录的文件路径（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 层目录的起点
BeginDeclare @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)用上面的方法也是被逼无奈，在效率上是无法挽回了，
不知大家有没有更好的方法？或我使用的这语句为什么出错，如何改正？

Answer 1

是因为有些目录的级数不足4级或者5级，这样你的函数返回的是负值，使substring函数出错

Answer 2

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

Answer 3

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
*/
godrop function f_test

Answer 4

比如说 不排序时，surpath顺序 
P:\06\xx\22\22\22\001.doc
P:\06\xx\10\1\1\001.doc
P:\06\xx\1\1\20\001.doc
P:\06\xx\1\1\12\001.doc
P:\06\xx\1\1\2\001.doc
P:\06\xx\1\1\1\001.doc
P:\06\xx\1\1\10\001.doc
P:\06\xx\2\1\1\001.doc现在需要按surpath排序的结果是：
P:\06\xx\1\1\1\001.doc
P:\06\xx\1\1\2\001.doc
P:\06\xx\1\1\10\001.doc
P:\06\xx\1\1\12\001.doc
P:\06\xx\1\1\20\001.doc
P:\06\xx\2\1\1\001.doc
P:\06\xx\10\1\1\001.doc
P:\06\xx\22\22\22\001.doc
也就是部分按字符串，部分按数字排，（具体的事那些部分由客户决定，此处暂不考虑，
在此就任务，第3、4、5层目录要按数字来排序，所有的文件可以不考虑，认为他们都相同。）

Answer 5

--按楼主的方法，加个判断正负的函数
create Function dbo.ifzero(@num int)
RETURNS INT
AsBegin
return case when @num>=0 then @num else 0 end
END
select id,surpath,edocno from HAM1B4PUS order by 
substring(surpath,0,dbo.ifzero(dbo.fnGet_nth_DirIndex(surpath,3)+1)),cast (substring(surpath,dbo.fnGet_nth_DirIndex(surpath,3)+1,dbo.ifzero(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.ifzero(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.ifzero(dbo.fnGet_nth_DirIndex(surpath,5+1)-dbo.fnGet_nth_DirIndex(surpath,5)-1)) as int)

Answer 6

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
godeclare @t table(path varchar(40))
insert into @t select 'P:\06\xx\22\22\22\001.doc'
insert into @t select 'P:\06\xx\10\1\1\001.doc'
insert into @t select 'P:\06\xx\1\1\20\001.doc'
insert into @t select 'P:\06\xx\1\1\12\001.doc'
insert into @t select 'P:\06\xx\1\1\2\001.doc'
insert into @t select 'P:\06\xx\1\1\1\001.doc'
insert into @t select 'P:\06\xx\1\1\10\001.doc'
insert into @t select 'P:\06\xx\2\1\1\001.doc'select * from @t order by dbo.f_test(path)
/*
path                                     
---------------------------------------- 
P:\06\xx\1\1\1\001.doc
P:\06\xx\1\1\2\001.doc
P:\06\xx\1\1\10\001.doc
P:\06\xx\1\1\12\001.doc
P:\06\xx\1\1\20\001.doc
P:\06\xx\2\1\1\001.doc
P:\06\xx\10\1\1\001.doc
P:\06\xx\22\22\22\001.doc
*/
godrop function f_test
go

Answer 7

--修改一下函数，只对3、4、5层目录做格式对齐
create function f_test(@code varchar(100))
returns varchar(100)
as
begin
    declare @n_code varchar(100),@i int
    set @i=1
    
    while @i<7 
    begin
        set @n_code=isnull(@n_code,'')+
          case 
            when @i>3 then right('0000000'+left(@code,charindex('\',@code)),8)
            else left(@code,charindex('\',@code))
          end
        
        set @code=stuff(@code,1,charindex('\',@code),'')
        set @i=@i+1
    end
    set @n_code=@n_code+@code
    
    return @n_code
end
godeclare @t table(path varchar(40))
insert into @t select 'P:\06\xx\22\22\22\001.doc'
insert into @t select 'P:\06\xx\10\1\1\001.doc'
insert into @t select 'P:\06\xx\1\1\20\001.doc'
insert into @t select 'P:\06\xx\1\1\12\001.doc'
insert into @t select 'P:\06\xx\1\1\2\001.doc'
insert into @t select 'P:\06\xx\1\1\1\001.doc'
insert into @t select 'P:\06\xx\1\1\10\001.doc'
insert into @t select 'P:\06\xx\2\1\1\001.doc'select path from @t order by dbo.f_test(path)
/*
path                                     
---------------------------------------- 
P:\06\xx\1\1\1\001.doc
P:\06\xx\1\1\2\001.doc
P:\06\xx\1\1\10\001.doc
P:\06\xx\1\1\12\001.doc
P:\06\xx\1\1\20\001.doc
P:\06\xx\2\1\1\001.doc
P:\06\xx\10\1\1\001.doc
P:\06\xx\22\22\22\001.doc
*/
godrop function f_test
go

Answer 8

我曾提议客户将数字的目录填充成 等长的字符串，前面补零，已经被否定了。
to：libin_ftsafe(子陌红尘：当libin告别ftsafe)
    
   你的方法确实好，谢谢！to：hrb2008() 
   因为我不是做纯数据库开发，是一个数据库应用的软件，这个功能在系统中返回必须要尽可能直接，也就只能在order by 上作文章，而整条语句 只能 像 子陌红尘 说的那样用 
select * from table order by dbo.f_test(path) 返回。所以你的方法我不能使用。再次感谢大家的帮忙，准备结贴。