SQL 排序问大家个比较棘手的问题:SQL 排序
1817A0Y003.1
1817A0Y003.2
1817A0Y003.3
1817A0Y003.4
1817A0Y003.5
1817A0Y003.6
1817A0Y003.7
1817A0Y003.8
1817A0Y003.9
1817A0Y003.10
1817A0Y003.11
1817A0Y003.12
1817A0Y003.13
1817A0Y003.14
1817A0Y003.15
1817A0Y003.16
1817A0Y003.17
1817A0Y003.18
1817A0Y003.19
1817A0Y003.20
1817A0Y003.21
1817A0Y003.22
1817A0Y003.23
1817A0Y003.24
1817A0Y003.25
1817A0Y003.26
1817A0Y003.27.1
1817A0Y003.27.2
1817A0Y003.27.3
1817A0Y003.28
1817A0Y003.29
1817A0Y003.30
1817A0Y003.31
1817A0Y003.32
1817A0Y003.33.1
1817A0Y003.33.2
1817A0Y003.33.3
1817A0Y003.34.1
1817A0Y003.34.2
1817A0Y003.34.3
1817A0Y003.35.1
1817A0Y003.35.2
1817A0Y003.35.3
1817A0Y003.36.1
1817A0Y003.36.2
1817A0Y003.36.3
1817A0Y003.37.1
1817A0Y003.37.2
1817A0Y003.37.3.1
1817A0Y003.37.3.2
1817A0Y003.37.3.3.1
1817A0Y003.37.3.3.2
1817A0Y003.38
1817A0Y003.39
1817A0Y003.40
1817A0Y003.41
1817A0Y003.42.1
1817A0Y003.42.2
1817A0Y003.42.3
1817A0Y003.43
1817A0Y003.1
1817A0Y003.2
1817A0Y003.3
1817A0Y003.4
1817A0Y003.5
1817A0Y003.6
1817A0Y003.7
1817A0Y003.8
1817A0Y003.9
1817A0Y003.10
1817A0Y003.11
1817A0Y003.12
1817A0Y003.13
1817A0Y003.14
1817A0Y003.15
1817A0Y003.16
1817A0Y003.17
1817A0Y003.18
1817A0Y003.19
1817A0Y003.20
1817A0Y003.21
1817A0Y003.22
1817A0Y003.23
1817A0Y003.24
1817A0Y003.25
1817A0Y003.26
1817A0Y003.27.1
1817A0Y003.27.2
1817A0Y003.27.3
1817A0Y003.28
1817A0Y003.29
1817A0Y003.30
1817A0Y003.31
1817A0Y003.32
1817A0Y003.33.1
1817A0Y003.33.2
1817A0Y003.33.3
1817A0Y003.34.1
1817A0Y003.34.2
1817A0Y003.34.3
1817A0Y003.35.1
1817A0Y003.35.2
1817A0Y003.35.3
1817A0Y003.36.1
1817A0Y003.36.2
1817A0Y003.36.3
1817A0Y003.37.1
1817A0Y003.37.2
1817A0Y003.37.3.1
1817A0Y003.37.3.2
1817A0Y003.37.3.3.1
1817A0Y003.37.3.3.2
1817A0Y003.38
1817A0Y003.39
1817A0Y003.40
1817A0Y003.41
1817A0Y003.42.1
1817A0Y003.42.2
1817A0Y003.42.3
1817A0Y003.43
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
drop table testcreate table test
(a varchar(50))
insert into test
select '1817A0Y003.1'
union
select '1817A0Y003.2'
union
select '1817A0Y003.3'
union
select '1817A0Y003.4'
union
select '1817A0Y003.5'
union
select '1817A0Y003.6'
union
select '1817A0Y003.7'
union
select '1817A0Y003.8'
union
select '1817A0Y003.9'
union
select '1817A0Y003.10'
union
select '1817A0Y003.11'
union
select '1817A0Y003.12'
union
select '1817A0Y003.37.3.1'
union
select '1817A0Y003.37.3.2'
union
select '1817A0Y003.37.3.3.1'
union
select '1817A0Y003.37.3.3.2'
create function Spit(@a nvarchar(100),@b char)
returns int
as
begin
declare @i int
set @i=0
declare @sql varchar(100)
set @sql=@a
while charindex(@b,@sql)>0
begin
set @i=@i+1
select @sql=substring(@sql,charindex(@b,@sql)+1,len(@sql)-charindex(@b,@sql))
end
return @i
endselect * from test
declare @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(a,'.')) 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
enddeclare @a varchar(100)
declare cur_test cursor for select a 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(a,'.')) from test where a=@a
set @i=1
declare @sql varchar(8000)
select @value=''''+substring(a,charindex('.',a)+1,len(a)-(charindex('.',a))+2)+'''' from test where a=@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 a='''+ @a + ''''
end
else
begin
set @sql='update test set '+ @b +'='+ @value +' where a='''+ @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_test
select a from test
order by a1,a2,a3,a4---------------------
1817A0Y003.1
1817A0Y003.2
1817A0Y003.3
1817A0Y003.4
1817A0Y003.5
1817A0Y003.6
1817A0Y003.7
1817A0Y003.8
1817A0Y003.9
1817A0Y003.10
1817A0Y003.11
1817A0Y003.12
1817A0Y003.37.3.1
1817A0Y003.37.3.2
1817A0Y003.37.3.3.1
1817A0Y003.37.3.3.2
union
select '1817A0Y003.37.3.4.1.1'
union
select '1817A0Y003.37.3.4.1.2'就不行了