Sql Server 中的版本号排序 [英] Version number sorting in Sql Server

问题描述

我有一个表存储版本如下

I have a table that stores the versions as under

Declare @tblVersion table(VersionNumber varchar(100))
 Insert into @tblVersion Values('1.3.1')
 Insert into @tblVersion Values('1.3.2.5')
 Insert into @tblVersion Values('1.4.1.7.12')
 Insert into @tblVersion Values('1.4.11.14.7')
 Insert into @tblVersion Values('1.4.3.109.1')
 Insert into @tblVersion Values('1.4.8.66')

 Select * From @tblVersion

VersionNumber
1.3.1
1.3.2.5
1.4.1.7.12
1.4.11.14.7
1.4.3.109.1
1.4.8.66

我的要求是我需要对它们进行排序，以便输出是

My requirement is that I need to sort them so that the output will be

VersionNumber
1.3.1
1.3.2.5
1.4.1.7.12
1.4.3.109.1
1.4.8.66
1.4.11.14.7

但是如果按它做一个简单的命令，它就不能按预期工作

But if do a simple order by it does not work as expected

Select VersionNumber
 From @tblVersion
 Order By VersionNumber

VersionNumber
1.3.1
1.3.2.5
1.4.1.7.12
1.4.11.14.7
1.4.3.109.1
1.4.8.66

需要帮助

推荐答案

Brain's Solution的实现

Implementation of Brain's Solution

Declare @tblVersion table(VersionNumber varchar(100)) 
 Insert into @tblVersion Values('1.3.1') 
 Insert into @tblVersion Values('1.3.2.5') 
 Insert into @tblVersion Values('1.4.1.7.12') 
 Insert into @tblVersion Values('1.4.11.14.7') 
 Insert into @tblVersion Values('1.4.3.109.1') 
 Insert into @tblVersion Values('1.4.8.66') 

 --Select * From @tblVersion 

 ;With CTE AS
 (
    Select 
        Rn = Row_Number() Over(Order By (Select 1))
        ,VersionNumber
    From @tblVersion
)
,CTESplit AS
(
    SELECT 
            F1.Rn,
            F1.VersionNumber,
            VersionSort = 
                            Case 
                                    When Len(O.VersionSort) = 1 Then '000' + O.VersionSort
                                    When Len(O.VersionSort) = 2 Then '00' + O.VersionSort
                                    When Len(O.VersionSort) = 3 Then '0' + O.VersionSort
                                    When Len(O.VersionSort) = 4 Then O.VersionSort
                            End

    FROM
    (
        SELECT *,
        cast('<X>'+replace(F.VersionNumber,'.','</X><X>')+'</X>' as XML) as xmlfilter from CTE F
    )F1
 CROSS APPLY
 ( 
    SELECT fdata.D.value('.','varchar(50)') as VersionSort  
    FROM f1.xmlfilter.nodes('X') as fdata(D)) O
 )
 ,CTE3 As(
Select 
        --Rn 
        --,
        VersionNumber
        ,SortableVersion = 
                            Stuff(
                                    (Select '.' + Cast(VersionSort As Varchar(100))
                             From CTESplit c2
                             Where c2.Rn = c1.Rn
                             For Xml Path('')),1,1,'')
From CTESplit c1
Group By c1.Rn,c1.VersionNumber
)
Select VersionNumber
From CTE3
Order By SortableVersion

这篇关于Sql Server 中的版本号排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！