如何在 T-SQL 中获取版本号 (varchar) 列的最大值

发布于 2024-09-05 14:56:59 字数 602 浏览 5 评论 0原文

我有一个这样定义的表：

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 或一些复杂的其他类似分割的逻辑。运行此查询：

SELECT MAX(Version) FROM my_table

将产生错误结果：

2.3.9，

而实际上应该是2.3.15。有什么好主意但不会太复杂吗？

原文

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

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

will yield the erroneous result:

2.3.9

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

听，心雨的声音 2024-09-12 14:56:59

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

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

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

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

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

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

回复收藏 0 原文