如何对“版本号”进行排序一般使用 SQL Server 查询的列

Question

我想知道我们当中的 SQL 天才是否可以向我伸出援助之手。

我在表 Versions 中有一个列 VersionNo ，其中包含“版本号”值等

VersionNo
---------
1.2.3.1
1.10.3.1
1.4.7.2

。

我希望对其进行排序，但不幸的是，当我执行标准 order by，它被视为一个字符串，因此顺序结果如下而

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

所以，我需要做的是按相反顺序对数字进行排序（例如，在 abcd 中，我需要按 d、c、b、a 排序才能获得正确的排序（ourder）。

但我对如何以通用方式实现这一目标感到困惑。当然，我可以使用各种 sql 函数分割字符串（例如 left、right、substring、len , charindex)，但我不能保证版本号始终由 4 部分组成。我可能有一个这样的清单：

VersionNo
---------
1.2.3.1
1.3
1.4.7.2
1.7.1
1.10.3.1
1.16.8.0.1

可以，有人有什么建议吗？我们将非常感谢您的帮助。

Answer 1

如果您使用的是 SQL Server 2008

select VersionNo from Versions order by cast('/' + replace(VersionNo , '.', '/') + '/' as hierarchyid);

什么是 hierarchyid

编辑：

2000 的解决方案，2005 年，2008 年：T- 的解决方案这里是 SQL 排序挑战。

挑战

Answer 2

根据 MySQL 的 SQL 引擎，情况如下：

SELECT versionNo FROM Versions
ORDER BY
SUBSTRING_INDEX(versionNo, '.', 1) + 0,
SUBSTRING_INDEX(SUBSTRING_INDEX(versionNo, '.', -3), '.', 1) + 0,
SUBSTRING_INDEX(SUBSTRING_INDEX(versionNo, '.', -2), '.', 1) + 0,
SUBSTRING_INDEX(versionNo, '.', -1) + 0;

对于 MySQL 版本 3.23.15 及以上

SELECT versionNo FROM Versions ORDER BY INET_ATON(ip);

Answer 3

如果可以，请更改架构，使版本有 4 列而不是 1 列。然后排序就很容易了。

Answer 4

另一种方法：

假设您只有 a、b、c、d，您也可以将数据分成列，并按 a、b、c、d（全部降序）进行排序并获取前 1 行

中将 1,2,3,4 更改为 1,2,3,4,5,6,7 等

如果您需要缩放到超过 d 表示 e,f,g...只需在查询Query :
查看演示

create table t (versionnumber varchar(255))
insert into t values
('1.0.0.505')
,('1.0.0.506')
,('1.0.0.507')
,('1.0.0.508')
,('1.0.0.509')
,('1.0.1.2')


; with cte as 
(
    select 
    column1=row_number() over (order by (select NULL)) ,
    column2=versionnumber
    from t
    )

select top 1
    CONCAT([1],'.',[2],'.',[3],'.',[4])
from 
(
    select 
        t.column1,
        split_values=SUBSTRING( t.column2, t1.N, ISNULL(NULLIF(CHARINDEX('.',t.column2,t1.N),0)-t1.N,8000)),
        r= row_number() over( partition by column1 order by t1.N) 
    from cte t 
        join
        (
            select 
                t.column2,
                1 as N 
            from cte t  
                UNION ALL
            select 
                t.column2,
                t1.N + 1 as N
            from cte t 
                join
                (
                 select 
                    top 8000
                        row_number() over(order by (select NULL)) as N 
                 from 
                    sys.objects s1 
                        cross join 
                   sys.objects s2 
                ) t1 
            on SUBSTRING(t.column2,t1.N,1) = '.'
         ) t1
          on t1.column2=t.column2
)a
pivot
( 
    max(split_values) for r in ([1],[2],[3],[4])
   )p
  order by [1] desc,[2] desc,[3] desc,[4] desc