建立个split函数,然后行转列,然后按列排序CREATE FUNCTION [dbo].[SplitStr] ( @string nvarchar(max), @symbol nvarchar(10) ) RETURNS @table TABLE(id int identity,value nvarchar(max)) AS begin DECLARE @splitlen int SET @splitlen=LEN(@symbol)-1 WHILE CHARINDEX(@symbol,@string)>0 BEGIN INSERT @table(value) VALUES(LEFT(@string,CHARINDEX(@symbol,@string)-1)) SET @string=STUFF(@string,1,CHARINDEX(@symbol,@string)+@splitlen,'') END INSERT @table(value) VALUES(@string) return enddeclare @tb table(ver nvarchar(20)) insert into @tb values('1.0.0.5'),('1.0.0.6'),('1.0.0.8'),('1.0.0.9'),('1.0.0.10'),('1.0.0.12')select * from @tb cross apply ( select convert(int,[1]) as v1,convert(int,[2]) as v2,convert(int,[3]) as v3,convert(int,[4]) as v4 from ( select * from master.dbo.splitStr(ver,'.') ) a pivot(max(value) for id in ([1],[2],[3],[4])) p ) b order by v1 desc,v2 desc,v3 desc,v4 desc(6 行受影响) ver v1 v2 v3 v4 -------------------- ----------- ----------- ----------- ----------- 1.0.0.12 1 0 0 12 1.0.0.10 1 0 0 10 1.0.0.9 1 0 0 9 1.0.0.8 1 0 0 8 1.0.0.6 1 0 0 6 1.0.0.5 1 0 0 5(6 行受影响)
(
@string nvarchar(max),
@symbol nvarchar(10)
)
RETURNS @table TABLE(id int identity,value nvarchar(max))
AS
begin
DECLARE @splitlen int
SET @splitlen=LEN(@symbol)-1
WHILE CHARINDEX(@symbol,@string)>0
BEGIN
INSERT @table(value) VALUES(LEFT(@string,CHARINDEX(@symbol,@string)-1))
SET @string=STUFF(@string,1,CHARINDEX(@symbol,@string)+@splitlen,'')
END
INSERT @table(value) VALUES(@string)
return
enddeclare @tb table(ver nvarchar(20))
insert into @tb values('1.0.0.5'),('1.0.0.6'),('1.0.0.8'),('1.0.0.9'),('1.0.0.10'),('1.0.0.12')select *
from @tb
cross apply (
select convert(int,[1]) as v1,convert(int,[2]) as v2,convert(int,[3]) as v3,convert(int,[4]) as v4
from (
select * from master.dbo.splitStr(ver,'.')
) a
pivot(max(value) for id in ([1],[2],[3],[4])) p
) b
order by v1 desc,v2 desc,v3 desc,v4 desc(6 行受影响)
ver v1 v2 v3 v4
-------------------- ----------- ----------- ----------- -----------
1.0.0.12 1 0 0 12
1.0.0.10 1 0 0 10
1.0.0.9 1 0 0 9
1.0.0.8 1 0 0 8
1.0.0.6 1 0 0 6
1.0.0.5 1 0 0 5(6 行受影响)
1 .把这些都分解后,然后存于临时表
2. 临时表中依次对每列进行比较得出最大值(当然也可以让列(第一列*10000+第二列*1000+第三列*100+第四列*10+第五列)相加,然后得到一个最大值就是最大版本号)