--> 测试数据:# 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 */
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)
--> 测试数据:# 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(默认排序)
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
*/
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)
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(默认排序)