根据@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;

我相信你的invoiceNo列有一些额外的字符，即使你使用了SUBSTR()，这些字符仍然存在。这会导致CAST失败，因为非数字字符仍然存在。如果这些额外的字符是空格，那么TRIM()函数可能会派上用场：

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

正如@Tah所指出的，如果需要的话，这里有一个链接可以帮助你删除 * 所有 * 字母数字字符：
MySQL strip non-numeric characters to compare

• • 更新日期：**

如果您想在一个查询中获得两个最大值，一种方法是执行您提到的两个查询的UNION：

SELECT 'invoiceMax', MAX(CAST(SUBSTR(TRIM(invoiceNo),3) AS UNSIGNED))
FROM selected_items 
WHERE invoiceNo RLIKE 'IN'
UNION
SELECT 'estimateMax', MAX(CAST(SUBSTR(TRIM(invoiceNo),3) AS UNSIGNED))
FROM selected_items 
WHERE invoiceNo RLIKE 'ES'

您可以使用条件聚集合并查询：

select
    max(case when invoiceNo rlike 'IN' then (your value here) end) maxIn,
    max(case when invoiceNo like 'ES' then (your value here) end) maxEs
from selected_items
where invoiceNo rlike 'IN'
or invoiceNo like 'ES'

另一种方法是使用子查询。我不确定哪个运行得更快，所以你可能想测试两个。

select
    (your in query) as maxIn,
    (your en query) as maxEn

// get max index value   codeinigter
        $res = $this->db->get($table)->result();
        $arr = array();
        foreach ($res as $val) {
            $str = preg_replace('/\D/', '', $val->id);
            $arr[] = $str;
        }
        $or = max($arr) + 1;

试试这个：

SELECT MAX(invoiceNo) FROM public.selected_items WHERE LENGTH(invoiceNo) = (SELECT MAX(LENGTH(invoiceNo)) FROM public.selected_items)

它应该也适用于PostgreSQL。