php - 如何从包含字母数字字符串的列中获取最大值？

Question

我有一张包含发票和估算编号的表格。发票编号如 "IN1000","IN1001","IN1002" 等。估算编号如 "ES101","ES102","ES103"。如何获得我的发票和估价的最大值？我还希望将其转换为 1000 之类的整数。

我尝试了以下查询:

SELECT Max(CAST (SUBSTR(invoiceNo,3) AS UNSIGNED)) FROM selected_items 
WHERE invoiceNo RLIKE 'IN';

当我运行此查询时，出现以下错误:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNSIGNED)) FROM selected_items WHERE invoiceNo RLIKE 'IN'' at line 1.

我正在使用类似的方法进行估算:

SELECT Max(CAST (SUBSTR(invoiceNo,3) AS UNSIGNED)) FROM selected_items
WHERE invoiceNo RLIKE 'ES';

如何在一个查询中执行这两项操作？感谢您的帮助。谢谢。

Answer 1

根据@FuzzyTree 和@TimBiegeleisen 的建议，您可以尝试以下查询。

Select (SELECT MAX(CAST(SUBSTR(TRIM(invoiceNo),3) AS UNSIGNED))
FROM selected_items 
WHERE invoiceNo RLIKE 'IN') as maxIN,
(SELECT MAX(CAST(SUBSTR(TRIM(invoiceNo),3) AS UNSIGNED))
FROM selected_items 
WHERE invoiceNo RLIKE 'ES') as maxES;