如何使用SQL Server查询一般地对“版本号"列进行排序 [英] How Can I Sort A 'Version Number' Column Generically Using a SQL Server Query
问题描述
我想知道我们中间的SQL天才是否能帮助我.
I wonder if the SQL geniuses amongst us could lend me a helping hand.
我在表Versions
中有一列VersionNo
,其中包含版本号"值,例如
I have a column VersionNo
in a table Versions
that contains 'version number' values like
VersionNo
---------
1.2.3.1
1.10.3.1
1.4.7.2
等
我正在寻找对它进行排序的方法,但是不幸的是,当我执行标准的order by
时,它被视为字符串,因此顺序显示为
I am looking to sort this, but unfortunately, when I do a standard order by
, it is treated as a string, so the order comes out as
VersionNo
---------
1.10.3.1
1.2.3.1
1.4.7.2
以下是我的追求:
VersionNo
---------
1.2.3.1
1.4.7.2
1.10.3.1
因此,我需要做的是按相反的顺序对数字进行排序(例如,在a.b.c.d中,我需要按d,c,b,a进行排序以获得正确的排序).
So, what I need to do is to sort by the numbers in reverse order (e.g. in a.b.c.d, I need to sort by d,c,b,a to get the correct sort ourder).
但是我对如何以一般方式实现这一目标感到困惑.当然,我可以使用各种sql函数(例如left
,right
,substring
,len
,charindex
)将字符串分割开,但是我不能保证总是有4个部分版本号.我可能有一个这样的列表:
But I am stuck as to how to achieve this in a GENERIC way. Sure, I can split the string up using the various sql functions (e.g. left
, right
, substring
, len
, charindex
), but I can't guarantee that there will always be 4 parts to the version number. I may have a list like this:
VersionNo
---------
1.2.3.1
1.3
1.4.7.2
1.7.1
1.10.3.1
1.16.8.0.1
可以,有人有什么建议吗?非常感谢您的帮助.
Can, does anyone have any suggestions? Your help would be much appreciated.
推荐答案
如果您使用的是SQL Server 2008
If You are using SQL Server 2008
select VersionNo from Versions order by cast('/' + replace(VersionNo , '.', '/') + '/' as hierarchyid);
Solutions for 2000, 2005, 2008: Solutions to T-SQL Sorting Challenge here.
这篇关于如何使用SQL Server查询一般地对“版本号"列进行排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!