如何查找带编号文本的最高版本并提取对应列的值

核心思路 #

先从translated_description列中提取出Supra后缀的数字，找到最大编号后，匹配对应的记录获取column2的值。以下是不同数据库的实现方案：

MySQL 实现

SELECT column2
FROM oe.product_descriptions
WHERE translated_description LIKE '%Supra%'
ORDER BY CAST(SUBSTRING(translated_description, LOCATE('Supra', translated_description) + 5) AS UNSIGNED) DESC
LIMIT 1;

• LOCATE('Supra', translated_description)：定位Supra在字符串中的起始位置
• SUBSTRING(..., +5)：提取Supra之后的所有字符（Supra长度为5）
• CAST(... AS UNSIGNED)：将提取出的字符串转为无符号整数
• 倒序排序后取第一条，即为最高编号对应的column2值

PostgreSQL 实现

SELECT column2
FROM oe.product_descriptions
WHERE translated_description LIKE '%Supra%'
ORDER BY CAST(SUBSTRING(translated_description FROM 'Supra(\d+)') AS INTEGER) DESC
LIMIT 1;

• SUBSTRING(...) FROM 'Supra(\d+)'：通过正则匹配提取Supra后的所有数字
• 转换为整数后倒序排序，取第一条记录的column2

SQL Server 实现

SELECT TOP 1 column2
FROM oe.product_descriptions
WHERE translated_description LIKE '%Supra%'
ORDER BY CAST(SUBSTRING(translated_description, CHARINDEX('Supra', translated_description) + 5, LEN(translated_description)) AS INT) DESC;

• CHARINDEX('Supra', ...)：定位Supra的起始位置
• SUBSTRING(...)：截取Supra之后的字符串并转为整数
• 倒序排序后取TOP 1得到目标值

处理多记录对应同一最高编号的情况 #

如果存在多条记录对应同一个最高编号，想要获取所有匹配的column2值，可使用子查询先锁定最大编号：

以MySQL为例：

SELECT column2
FROM oe.product_descriptions
WHERE translated_description LIKE '%Supra%'
AND CAST(SUBSTRING(translated_description, LOCATE('Supra', translated_description) + 5) AS UNSIGNED) = (
    SELECT MAX(CAST(SUBSTRING(translated_description, LOCATE('Supra', translated_description) + 5) AS UNSIGNED))
    FROM oe.product_descriptions
    WHERE translated_description LIKE '%Supra%'
);

内容的提问来源于stack exchange，提问作者katarzynat