如何在T-SQL中获取版本号(varchar)列的MAX值 [英] How to get MAX value of a version-number (varchar) column in T-SQL

问题描述

我有一个这样定义的表:

I have a table defined like this:

Column:  Version     Message
Type:    varchar(20) varchar(100)
----------------------------------
Row 1:    2.2.6       Message 1
Row 2:    2.2.7       Message 2
Row 3:    2.2.12      Message 3
Row 4:    2.3.9       Message 4
Row 5:    2.3.15      Message 5

我想编写一个T-Sql查询，该查询将获取有关MAX版本号的消息，其中版本"列表示软件版本号.即，2.2.12大于2.2.7，而2.3.15大于2.3.9，依此类推.不幸的是，我无法想到一种简单的方法，而无需使用CHARINDEX或某些复杂的类似分裂的逻辑.运行此查询:

I want to write a T-Sql query that will get message for the MAX version number, where the "Version" column represents a software version number. I.e., 2.2.12 is greater than 2.2.7, and 2.3.15 is greater than 2.3.9, etc. Unfortunately, I can't think of an easy way to do that without using CHARINDEX or some complicated other split-like logic. Running this query:

SELECT MAX(Version) FROM my_table

会产生错误的结果:

2.3.9

实际上应该是 2.3.15 .有什么聪明的想法不会变得太复杂吗?

When it should really be 2.3.15. Any bright ideas that don't get too complex?

推荐答案

一种解决方案是使用表值拆分函数将版本拆分为行，然后将其合并为列，以便您可以执行以下操作:

One solution would be to use a table-valued split function to split the versions into rows and then combine them back into columns so that you can do something like:

Select TOP 1 Major, Minor, Build
From ( ...derived crosstab query )
Order By Major Desc, Minor Desc, Build Desc

实际上，另一种方法是使用PARSENAME函数，该函数用于拆分对象名称:

Actually, another way is to use the PARSENAME function which was meant to split object names:

Select TOP 1 Version
From Table
Order By Cast(Parsename( Z.Version , 3 ) As Int) Desc
    , Cast(Parsename( Z.Version , 2 ) As Int) Desc
    , Cast(Parsename( Z.Version , 1 ) As Int) Desc

这篇关于如何在T-SQL中获取版本号(varchar)列的MAX值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！