实施大脑解决方案
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 c1Group By c1.Rn,c1.VersionNumber)Select VersionNumberFrom CTE3Order By SortableVersion
