版本号排序问题

[junoon]

默认排序
3.1.10
3.1.4
3.10.4
3.2.11
3.2.2

期望结果
3.1.4
3.1.10
3.2.2
3.2.11
3.10.4

请问使用mssql如何实现期望排序效果

[SQLCenter]

--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(ver varchar(10))
insert into #
select '3.1.10' union all
select '3.1.4' union all
select '3.10.4' union all
select '3.2.11' union all
select '3.2.2'

-- 不超过4段可以这样排
select * from # order by convert(int,parsename(ver,4)),convert(int,parsename(ver,3)),convert(int,parsename(ver,2)),convert(int,parsename(ver,1))

/*
ver
----------
3.1.4
3.1.10
3.2.2
3.2.11
3.10.4
*/

[cjzm83]

declare @tb table (s varchar(100))

insert @tb
select '3.1.10'
union all
select '3.1.40'
union all
select '3.10.4'
union all
select '113.2.11'
union all
select '3.1.2234'
union all
select '3.2.2'
union all
select '430.2.2'
union all
select '300.27.2'
select * from @tb
order by cast(left(s,charindex('.',s)-1) as int),
cast(left(stuff(s,1,charindex('.',s),''),charindex('.',stuff(s,1,charindex('.',s),''))-1) as int),
cast(right(s,len(stuff(s,1,charindex('.',s),'')) - charindex('.',stuff(s,1,charindex('.',s),''))) as int)

[junoon]

额，不定长的哦。。

[sanny_txx]

不定长的就写动态的吧，改改#1楼的就行

[SQLCenter]

--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(ver varchar(10))
insert into #
select '4.0' union all
select '3.1.10' union all
select '3.1.4' union all
select '3.10.4' union all
select '3.2.11' union all
select '3.2.2'

-- 长短混编的要这样
select * from # order by
	convert(int,reverse(parsename(reverse(ver),1))),
	convert(int,reverse(parsename(reverse(ver),2))),
	convert(int,reverse(parsename(reverse(ver),3))),
	convert(int,reverse(parsename(reverse(ver),4)))

/*
3.1.4
3.1.10
3.2.2
3.2.11
3.10.4
4.0
*/

[王向飞]

--用一个函数
create function dbo.getPai(@a varchar(20))
returns varchar(20)
As
begin
declare @s varchar(20)
set @s = ''
while charindex('.',@a) > 0
begin
 set @s = @s + right('0000' + left(@a,charindex('.',@a)),4)
 set @a = right(@a,len(@a)-charindex('.',@a))
end
set @s = @s + right('0000' + @a,3)
return @s
end


--> 测试数据：信息表
if object_id('信息表') is not null
drop table 信息表

---->建表
create table 信息表([默认排序] nvarchar(20))
insert 信息表
select '3.1.10' union all
select '3.1.4' union all
select '3.10.4' union all
select '3.2.11' union all
select '3.2.2'


--> 查询结果
SELECT * FROM 信息表 
order by dbo.getPai(默认排序)

[duanzhi1984]

[Quote=引用 6 楼 wxf163 的回复:]
SQL code

--用一个函数
create function dbo.getPai(@a varchar(20))
returns varchar(20)
As
begin
declare @s varchar(20)
set @s = ''
while charindex('.',@a) > 0
begin
set @s = @s + right('0000' + ……
[/Quote]


以上不错

[junoon]

[Quote=引用 6 楼 wxf163 的回复:]
SQL code

--用一个函数
create function dbo.getPai(@a varchar(20))
returns varchar(20)
As
begin
declare @s varchar(20)
set @s = ''
while charindex('.',@a) > 0
begin
set @s = @s + right('0000' + ……
[/Quote]确实是正解