如何选择混合字符串/整数列的最大值？

Question

假设我有一个表，其中包含发票号列，数据类型为VARCHAR，包含混合的字符串/整数值，例如：

invoice_number
**************
    HKL1
    HKL2
    HKL3
    .....
    HKL12
    HKL13
    HKL14
    HKL15

我试图选择其中的最大值，但它返回的是“HKL9”，而不是最高值“HKL15”。

SELECT MAX( invoice_number )
FROM `invoice_header`

- CairoCoder

Answer 1

HKL9（字符串）大于HKL15，因为它们是作为字符串进行比较的。解决问题的一种方法是定义一个列函数，仅返回发票号码的数字部分。

如果您所有的发票号码都以HKL开头，则可以使用以下方法：

如果您所有的发票号码都以HKL开头，则可以使用：

SELECT MAX(CAST(SUBSTRING(invoice_number, 4, length(invoice_number)-3) AS UNSIGNED)) FROM table

使用发票号码，排除前三个字符，将其转换为整数，并从中选择最大值。

- nakosspy

---

感谢@nakosspy，但我该如何使用php/mysql实现呢？ - CairoCoder

该语句是标准的SQL语句，您可以像执行其他语句一样执行它。 - nakosspy

我明白了，它是：SELECT max(substring(invoice_number, 4, length(invoice_number)-3)) FROM table。 - CairoCoder

SELECT MAX(CAST(SUBSTRING(invoice_number, 4, LENGTH(invoice_number)-3) AS UNSIGNED)) FROM table - CairoCoder

Answer 2

select ifnull(max(CONVERT(invoice_number, SIGNED INTEGER)), 0)
from invoice_header 
where invoice_number REGEXP '^[0-9]+$'

- Sandeep

---

这是唯一正确的答案，因为它可以处理不同格式的发票号码，并且无需修改数据库即可运行。 - Vincent

如果发票号码前面有字母，则此代码无法提取发票号码的数字部分 - DarkSide

Answer 3

经过一段时间的搜索，我找到了最简单的解决方案。

select MAX(CAST(REPLACE(REPLACE(invoice_number , 'HKL', ''), '', '') as int)) from invoice_header

- Ketan Dubey

Answer 4

这也应该可以工作。

SELECT invoice_number
FROM invoice_header
ORDER BY LENGTH( invoice_number) DESC,invoice_number DESC 
LIMIT 0,1

- irfandar

Answer 5

你的问题更多是定义和设计方面的。选择具有最高ID或日期的发票号码，或者——如果它们真的与“最高发票号码”不相关——定义一个额外的列，这个列与发票号码相关，并且足够简单，让可怜的数据库理解。

select INVOICE_NUMBER 
from INVOICE_HEADER
order by ID desc limit 1;

并不是数据库不够聪明，而是你提出了错误的问题。

- Thomas W

---

谢谢@Thomas W，但如果我问错了，我该怎么问呢？ - CairoCoder

我认为它也是按照降序排序的。 - CairoCoder

最高编号的发票应该是最近的；按ID或日期排序。您不应该期望数据库解码任意字符串，但它们在执行主键索引扫描时非常快速 :) - Thomas W

Answer 6

以下查询可用：

选择max(cast((CASE WHEN max_no NOT LIKE '%[^0-9]%' THEN max_no END) as int)) AS max_int_no from table1

- Ambreen Haleem

---

当回答一个旧问题时，如果您包含一些上下文来解释您的答案如何有帮助，那么您的答案对其他StackOverflow用户将会更有用。请参阅：如何撰写好答案。 - David Buck

Answer 7

对于 MySQL 数据库，我使用以下代码：

SELECT CONCAT('HK','L',MAX(ABS(SUBSTRING_INDEX(invoice_number,'L', -1))))AS maxInvoice FROM invoice_header

因为所有的发票号码都可以使用字母“L”作为分隔符，所以可以使用SUBSTRING_INDEX()函数来拆分发票号码，然后使用concat()函数将它们再次连接起来以获取最大的发票号码。

- Chauncey Lee