MySQL 如何选择混合字符串/整数列的最大值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16496874/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to select max of mixed string/int column?
提问by CairoCoder
Lets say that I have a table which contains a column for invoice number, the data type is VARCHAR with mixed string/int values like:
假设我有一个包含发票编号列的表，数据类型是 VARCHAR，带有混合字符串/int 值，例如：
invoice_number
**************
HKL1
HKL2
HKL3
.....
HKL12
HKL13
HKL14
HKL15
I tried to select max of it, but it returns with "HKL9", not the highest value "HKL15".
我试图选择它的最大值，但它返回“HKL9”，而不是最高值“HKL15”。
SELECT MAX( invoice_number )
FROM `invoice_header`
回答by nakosspy
HKL9(string) is greater than HKL15, because they are compared as strings. One way to deal with your problem is to define a column function that returns only the numeric part of the invoice number.
HKL9(string) 大于HKL15，因为它们作为字符串进行比较。处理您的问题的一种方法是定义一个列函数，该函数仅返回发票编号的数字部分。
If all your invoice numbers start with HKL, then you can use:
如果您的所有发票编号都以 开头HKL，则您可以使用：
SELECT MAX(CAST(SUBSTRING(invoice_number, 4, length(invoice_number)-3) AS UNSIGNED)) FROM table
It takes the invoice_number excluding the 3 first characters, converts to int, and selects max from it.
它采用不包括前 3 个字符的 invoice_number，转换为 int，并从中选择 max。
回答by Sandeep
select ifnull(max(CONVERT(invoice_number, SIGNED INTEGER)), 0)
from invoice_header
where invoice_number REGEXP '^[0-9]+$'
回答by irfandar
This should work also
这也应该有效
SELECT invoice_number
FROM invoice_header
ORDER BY LENGTH( invoice_number) DESC,invoice_number DESC
LIMIT 0,1
回答by Thomas W
Your problem is more one of definition & design.
您的问题更多是定义和设计之一。
Select the invoice number with highest ID or DATE,or -- if those reallydon't correlate with "highest invoice number" -- define an additional column, which does correlate with invoice-number and is simple enough for the poor database to understand.
选择具有最高 ID 或 DATE 的发票编号，或者 - 如果这些确实与“最高发票编号”无关 - 定义一个附加列，该列与发票编号相关并且足够简单以供糟糕的数据库理解.
select INVOICE_NUMBER
from INVOICE_HEADER
order by ID desc limit 1;
It's not that the database isn't smart enough.. it's that you're asking it the wrong question.
不是数据库不够智能..而是你问错了问题。
回答by Ketan Dubey
After a while of searching I found the easiest solution to this.
经过一段时间的搜索，我找到了最简单的解决方案。
select MAX(CAST(REPLACE(REPLACE(invoice_number , 'HKL', ''), '', '') as int)) from invoice_header
回答by Ambreen Haleem
Below query can be used:
可以使用以下查询：
select max(cast((CASE WHEN max_no NOT LIKE '%[^0-9]%' THEN max_no END) as int)) AS max_int_no from table1
select max(cast((CASE WHEN max_no NOT LIKE '%[^0-9]%' THEN max_no END) as int)) AS max_int_no from table1
