postgresql 在Postgres中排序Varchar版本

yfjy0ee7  于 2022-12-18  发布在  PostgreSQL
关注(0)|答案(1)|浏览(166)

我有列版本(Varchar)的表包,不能像这样对字符串进行排序。
有什么方法可以让我得到最新的软件包版本吗?
数据格式如下:

id | version
128,3.0.5-1
128,3.0.6-1
128,3.0.7-1
128,3.0.8-1
128,3.0.9-1
128,3.0.13-2
128,3.0.4-1-1
128,3.0.10-1
128,3.0.11-2
128,3.0.11-1

我得出了一个类似的解决方案:

SELECT  version
FROM packages
ORDER BY
    -- First, sort by the first number in the version
    SUBSTRING(version, '^[0-9]+') DESC,
    -- Then, sort by the second number in the version
    SUBSTRING(version, '[0-9]+\\.[0-9]+\\.([0-9]+)-') DESC,
    -- Then, sort by the third number in the version
    CAST(SUBSTRING(version, '[0-9]+\\.[0-9]+\\.[0-9]+-([0-9]+)') AS INTEGER) DESC,
    -- Finally, sort by the fourth number in the version
    SUBSTRING(version, '[0-9]+\\.[0-9]+\\.[0-9]+-[0-9]+\\.([0-9]+)') DESC
    -- Return only the first row

这将返回:

3.0.5-1
3.0.6-1
3.0.7-1
3.0.8-1
3.0.9-1
3.0.13-2
3.0.4-1-1
3.0.10-1
3.0.11-2
3.0.11-1


在3.0.10-1之前,这似乎工作正常,所以问题是它遵循x.x.x-x模式,但当x是两位数时不工作。
有没有什么方法可以在查询本身中实现这一点?如果有人能给予我提示或解决方案,我将非常感激。

xuo3flqw

xuo3flqw1#

split_part()更适合于这种将字符串拆分为元素的方法。
您还需要将每个元素转换为数字以获得正确的排序:

select *
from packages
order by split_part(version, '.', 1)::int,
         split_part(version, '.', 2)::int,
         string_to_array(split_part(version, '.', 3), '-')::int[]

第三部分(例如11-1')被转换成可以直接排序的整数数组。
如果你only3.0.5这样的版本号,你可以通过将整个版本号转换成一个整数数组来排序。你可以用一个点来代替减号:

select *
from packages
order by string_to_array(replace(version, '-', '.'), '.')::int[];

并且只获取最新的软件包版本:

select *
from packages
order by string_to_array(replace(version, '-', '.'), '.')::int[] desc
fetch first row only;


演示:https://dbfiddle.uk/mBw2-OQC

相关问题