拆分字符串并在 mssql 中返回最大值 [英] Split a string and return greatest in mssql

问题描述

我需要找到一种方法来获取版本号最高的数据.

I need to find a way to get the data with the highest 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 get the record with the versionnumber 2.2.1. Need some help with the sql though :)

感谢您的帮助

推荐答案

试试这个:

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；为了弥补这一点，您必须填充数字:

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

输出:

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

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

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

这篇关于拆分字符串并在 mssql 中返回最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！