分割字符串并返回mssql中最大的

发布于 2024-11-26 20:56:29 字数 429 浏览 0 评论 0原文

我需要找到一种方法来获取具有最高versionNumber的数据。

这是我的数据库设计：

VERSIONNUMBER - varchar(15)
DOWNLOADPATH - varchar(100)

假设我有这样的记录：

VERSIONNUMBER -------- DOWNLOADPATH
1.1.2                  a.com
1.1.3                  b.com
2.1.4                  c.com
2.1.5                  d.com
2.2.1                  e.com

我需要获取版本号为 2.2.1 的记录。不过需要一些关于 sql 的帮助:)

谢谢你的帮助

原文

I need to find a way to get the data with the highest versionNumber.

Here is my database design:

VERSIONNUMBER - varchar(15)
DOWNLOADPATH - varchar(100)

Lets say I have records like:

VERSIONNUMBER -------- DOWNLOADPATH
1.1.2                  a.com
1.1.3                  b.com
2.1.4                  c.com
2.1.5                  d.com
2.2.1                  e.com

I need to get the record with the versionnumber 2.2.1. Need some help with the sql though :)

Thank you for any help

分享到QQ

分享到微博

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

发布评论

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

放血 2024-12-03 20:56:29

试试这个：

with a as
(
    select * from (values
    ('1.1.2'),('1.1.3'),('2.1.4 '), ('2.1.5'), ('2.2.1') ) as b(c)
)
select c, PARSENAME(c,1),PARSENAME(c,2), PARSENAME(c,3)
from a
order by 
convert(int,PARSENAME(c,3)),
convert(int,PARSENAME(c,2)),
convert(int,PARSENAME(c,1))

灵感来自： http://www.sql-server -helper.com/tips/sort-ip-address.aspx

with a as
(
    select * from (values
    ('1.1.2'),('1.1.3'),('2.1.4 '), ('2.1.5'), ('2.2.1') ) as b(c)
),
x as 
(
    select c, 
       convert(int,PARSENAME(c,3)) * 100 
       + convert(int,PARSENAME(c,2)) * 10 
       + convert(int,PARSENAME(c,1)) * 1 as the_value
    from a
)
select c from x where the_value = (select MAX(the_value) from x)

在软件开发中，通常会发现有两位数字的次版本号，版本号与数字值没有任何关系，因此版本 1.12大于1.5；为了弥补这一点，您必须充分填充数字：

    -- Use this, the query above is not future-proof :-)
with a as
(
    select * from (values
    ('2.1.4 '), ('2.1.12'), ('2.1.5'), ('2.2.1') ) as b(c)
),
x as 
(
    select c, 
       convert(int,PARSENAME(c,3)) * 100*100*100 
       + convert(int,PARSENAME(c,2)) * 100*100 
       + convert(int,PARSENAME(c,1)) * 100 as the_value
    from a
)
select c, the_value from x   
order by the_value

输出：

2.1.4   2010400
2.1.5   2010500
2.1.12  2011200
2.2.1   2020100

如果您不考虑这一点（如以下查询）：

with a as
(
    select * from (values
    ('2.1.4 '), ('2.1.12'), ('2.1.5'), ('2.2.1') ) as b(c)
),
x as 
(
    select c, 
       convert(int,PARSENAME(c,3)) * 100
       + convert(int,PARSENAME(c,2)) * 10
       + convert(int,PARSENAME(c,1)) * 1 as the_value
    from a
)
select c, the_value from x   
order by the_value;


    -- KorsG's answer has a bug too
with a as
(
    select * from (values
    ('2.1.4 '), ('2.1.12'), ('2.1.5'), ('2.2.1') ) as b(c)
),
x as 
(
    select c, 
       CAST(REPLACE(c, '.', '') AS int) as the_value
    from a
)
select c, the_value from x   
order by the_value

这两个查询将产生相同（不正确）的输出：

c           the_value
2.1.4   214
2.1.5   215
2.2.1   221
2.1.12  222

2.2.1 和 2.1.12 的值重叠。当您仅删除点并直接将结果字符串转换为 int 时，也会发生这种情况。 2.1.12变为2112，2.2.1变为2221。 2.2.1大于2.1.12，不小于

Try this:

with a as
(
    select * from (values
    ('1.1.2'),('1.1.3'),('2.1.4 '), ('2.1.5'), ('2.2.1') ) as b(c)
)
select c, PARSENAME(c,1),PARSENAME(c,2), PARSENAME(c,3)
from a
order by 
convert(int,PARSENAME(c,3)),
convert(int,PARSENAME(c,2)),
convert(int,PARSENAME(c,1))

Inspired from: http://www.sql-server-helper.com/tips/sort-ip-address.aspx

with a as
(
    select * from (values
    ('1.1.2'),('1.1.3'),('2.1.4 '), ('2.1.5'), ('2.2.1') ) as b(c)
),
x as 
(
    select c, 
       convert(int,PARSENAME(c,3)) * 100 
       + convert(int,PARSENAME(c,2)) * 10 
       + convert(int,PARSENAME(c,1)) * 1 as the_value
    from a
)
select c from x where the_value = (select MAX(the_value) from x)

In software development, it is typical to find a minor version number that has two digits in it, the version's number don't have any bearing with number's value, thus version 1.12 is greater than 1.5; to compensate for that, you must pad the digits adequately:

    -- Use this, the query above is not future-proof :-)
with a as
(
    select * from (values
    ('2.1.4 '), ('2.1.12'), ('2.1.5'), ('2.2.1') ) as b(c)
),
x as 
(
    select c, 
       convert(int,PARSENAME(c,3)) * 100*100*100 
       + convert(int,PARSENAME(c,2)) * 100*100 
       + convert(int,PARSENAME(c,1)) * 100 as the_value
    from a
)
select c, the_value from x   
order by the_value

Output:

2.1.4   2010400
2.1.5   2010500
2.1.12  2011200
2.2.1   2020100

If you don't take that into consideration(as with the following query):

with a as
(
    select * from (values
    ('2.1.4 '), ('2.1.12'), ('2.1.5'), ('2.2.1') ) as b(c)
),
x as 
(
    select c, 
       convert(int,PARSENAME(c,3)) * 100
       + convert(int,PARSENAME(c,2)) * 10
       + convert(int,PARSENAME(c,1)) * 1 as the_value
    from a
)
select c, the_value from x   
order by the_value;


    -- KorsG's answer has a bug too
with a as
(
    select * from (values
    ('2.1.4 '), ('2.1.12'), ('2.1.5'), ('2.2.1') ) as b(c)
),
x as 
(
    select c, 
       CAST(REPLACE(c, '.', '') AS int) as the_value
    from a
)
select c, the_value from x   
order by the_value

Those two queries will yield the same (incorrect) output:

c           the_value
2.1.4   214
2.1.5   215
2.2.1   221
2.1.12  222

The 2.2.1 and 2.1.12's value overlapped. That also happens when you merely remove the dots and directly convert the resulting string to int. 2.1.12 become two thousand one hundred twelve, 2.2.1 become two hundred twenty one. 2.2.1 is greater than 2.1.12, not less than

回复收藏 0 原文

莫言歌 2024-12-03 20:56:29

select top 1 DOWNLOADPATH
from YourTable
order by cast(parsename(VERSIONNUMBER, 3) as int) desc,
         cast(parsename(VERSIONNUMBER, 2) as int) desc,
         cast(parsename(VERSIONNUMBER, 1) as int) desc

select top 1 DOWNLOADPATH
from YourTable
order by cast(parsename(VERSIONNUMBER, 3) as int) desc,
         cast(parsename(VERSIONNUMBER, 2) as int) desc,
         cast(parsename(VERSIONNUMBER, 1) as int) desc

回复收藏 0 原文

夏有森光若流苏 2024-12-03 20:56:29

或者，您可以使用排名，而不是将每个数字组相乘：

with a as
(
    select * from (values
        ('2.1.4 '), ('2.1.12'), ('2.1.5'), ('2.2.1') 
    ) as b(c)     
),
x as
(
select c, 
    Ranking = RANK() over(order by convert(int,PARSENAME(c,3)), convert(int,PARSENAME(c,2)), convert(int,PARSENAME(c,1))) 
from a
)
select * from x 
    order by ranking

产量：

c   Ranking
2.1.4   1
2.1.5   2
2.1.12  3
2.2.1   4

最终查询：

with a as
(
    select * from (values
        ('2.1.4 '), ('2.1.12'), ('2.1.5'), ('2.2.1') 
    ) as b(c)     
),
x as
(
select c, 
    Ranking = RANK() over(order by convert(int,PARSENAME(c,3)), convert(int,PARSENAME(c,2)), convert(int,PARSENAME(c,1))) 
from a
)
select * 
from x  
where Ranking = (select MAX(ranking) from x)

输出：

c   Ranking
2.2.1   4

另一种简单的方法，降序排序，然后只获取第一行：

with a as
(
    select * from (values
        ('2.1.4 '), ('2.1.12'), ('2.1.5'), ('2.2.1') ) as b(c)     
),
x as
(
select c, 
    Ranking = RANK() 
        over(order by 
            convert(int,PARSENAME(c,3)) desc, 
            convert(int,PARSENAME(c,2)) desc, 
            convert(int,PARSENAME(c,1)) desc) 
from a
)
select * 
from x  
where Ranking = 1

Alternatively, instead of multiplying each digit group, you can use ranking:

with a as
(
    select * from (values
        ('2.1.4 '), ('2.1.12'), ('2.1.5'), ('2.2.1') 
    ) as b(c)     
),
x as
(
select c, 
    Ranking = RANK() over(order by convert(int,PARSENAME(c,3)), convert(int,PARSENAME(c,2)), convert(int,PARSENAME(c,1))) 
from a
)
select * from x 
    order by ranking

Yields:

c   Ranking
2.1.4   1
2.1.5   2
2.1.12  3
2.2.1   4

Final query:

with a as
(
    select * from (values
        ('2.1.4 '), ('2.1.12'), ('2.1.5'), ('2.2.1') 
    ) as b(c)     
),
x as
(
select c, 
    Ranking = RANK() over(order by convert(int,PARSENAME(c,3)), convert(int,PARSENAME(c,2)), convert(int,PARSENAME(c,1))) 
from a
)
select * 
from x  
where Ranking = (select MAX(ranking) from x)

Output:

c   Ranking
2.2.1   4

Another simple approach, sort descending then just get the first row:

with a as
(
    select * from (values
        ('2.1.4 '), ('2.1.12'), ('2.1.5'), ('2.2.1') ) as b(c)     
),
x as
(
select c, 
    Ranking = RANK() 
        over(order by 
            convert(int,PARSENAME(c,3)) desc, 
            convert(int,PARSENAME(c,2)) desc, 
            convert(int,PARSENAME(c,1)) desc) 
from a
)
select * 
from x  
where Ranking = 1

回复收藏 0 原文

情话难免假 2024-12-03 20:56:29

如果您使用的是 SQL Server 2008，则可以利用 HIERARCHYID 数据类型。

SELECT VersionNumber, DownloadPath
FROM (VALUES
    ('1.1.2','a.com'),
    ('1.1.3','b.com'),
    ('2.1.4','c.com'),
    ('2.1.5','d.com'),
    ('2.2.1','e.com')        
     ) AS T(VersionNumber, DownloadPath)
ORDER  BY CAST('/' + VersionNumber + '/' AS HIERARCHYID) DESC

If you are on SQL Server 2008 you can lever the HIERARCHYID datatype.

SELECT VersionNumber, DownloadPath
FROM (VALUES
    ('1.1.2','a.com'),
    ('1.1.3','b.com'),
    ('2.1.4','c.com'),
    ('2.1.5','d.com'),
    ('2.2.1','e.com')        
     ) AS T(VersionNumber, DownloadPath)
ORDER  BY CAST('/' + VersionNumber + '/' AS HIERARCHYID) DESC

回复收藏 0 原文

天涯沦落人 2024-12-03 20:56:29

这会起作用，但它并不漂亮 - 我肯定会考虑更改存储版本号的方式。

concat( 
right(concat(repeat("0",5), substring_index(VERSIONNUMBER,".",1)),5),
right(concat(repeat("0",5), substring_index(substring_index(VERSIONNUMBER,".",2),".",-1)),5),
right(concat(repeat("0",5), substring_index(VERSIONNUMBER,".",-1)),5))

基本上，它将 "1.24.937" 转换为 "000010002400937"，然后将其作为字符串正确排序。

This will work, but it ain't pretty - I would definitely consider changing the way you store version numbers.

concat( 
right(concat(repeat("0",5), substring_index(VERSIONNUMBER,".",1)),5),
right(concat(repeat("0",5), substring_index(substring_index(VERSIONNUMBER,".",2),".",-1)),5),
right(concat(repeat("0",5), substring_index(VERSIONNUMBER,".",-1)),5))

Basically it turns "1.24.937" into "000010002400937", which then sorts properly as a string.

回复收藏 0 原文

糖粟与秋泊 2024-12-03 20:56:29

就我个人而言，我喜欢 @Mikael 的版本，但显然这对于其他 RDBMS 来说不太可移植......

这个怎么样？快速而肮脏，适用于三个数字的版本（如您的示例中所示）。

这里的技巧是要意识到“2.59”，例如，是一个有效的数字，只是不是一个有效的整数，所以您只需要拆分字符串在一处——您需要主版本号，以及其余的。这很可怕，但当它突然出现在我的脑海中时，我想我应该分享它，因为它至少短而且很丑陋。

SELECT 
   TOP 1 downloadpath 
FROM 
   version_table 
ORDER BY 
   CAST(LEFT(VERSIONNUMBER, CHARINDEX( '.', VERSIONNUMBER) - 1) AS INTEGER) DESC,
   CAST(SUBSTRING(VERSIONNUMBER, CHARINDEX( '.', VERSIONNUMBER) + 1, 100) AS FLOAT) DESC

当然，真正的答案是更改您的数据库设计，以拆分版本号或包含内部版本号或类似的内容......

Personally, I like @Mikael's version, but obviously that's not quite as portable to other RDBMSes...

How about this? Quick and dirty, and works for three-number versions (as given in your example.)

The hack here is to realise that "2.59", for example, is a valid number, just not a valid integer, so you only have to split the string in one place -- you want the major version number, and the rest. This is hideous, but when it sprang to mind, I figured I'd share it, as it is at least short and hideous.

SELECT 
   TOP 1 downloadpath 
FROM 
   version_table 
ORDER BY 
   CAST(LEFT(VERSIONNUMBER, CHARINDEX( '.', VERSIONNUMBER) - 1) AS INTEGER) DESC,
   CAST(SUBSTRING(VERSIONNUMBER, CHARINDEX( '.', VERSIONNUMBER) + 1, 100) AS FLOAT) DESC

Of course, the real answer is to change your database design to either split out the version numbers or include a build number or something like that...

回复收藏 0 原文

听你说爱我 2024-12-03 20:56:29

SELECT downloadpath FROM TABLE
WHERE versionnumber = (SELECT MAX(VersionNumber) FROM TABLE)

可能还有一种更漂亮的方法来做到这一点。

SELECT downloadpath FROM TABLE
WHERE versionnumber = (SELECT MAX(VersionNumber) FROM TABLE)

There might be a prettier way to do it as well.

回复收藏 0 原文

~没有更多了~

关于作者

雨落□心尘

暂无简介

0 文章

0 评论

24 人气

关注发私信

友情链接

文江博客

分割字符串并返回mssql中最大的

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

发布评论

评论（7）

关于作者

相关话题

热门标签

推荐作者

Gabu-gabumon

qq_CgiN62

荔枝明

赏烟花じ飞满天

独守阴晴ぅ圆缺

¤→小豸慧

友情链接

分割字符串并返回mssql中最大的

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

发布评论

评论（7）

关于作者

相关话题

热门标签

推荐作者

Gabu-gabumon

qq_CgiN62

荔枝明

赏烟花じ飞满天

独守阴晴ぅ圆缺

¤→小豸慧

友情链接

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