如何选择最大的混合字符串/整数列? [英] how to select max of mixed string/int column?
问题描述
让我们说我有一个表,其中包含发票编号列,数据类型为VARCHAR,具有混合的字符串/整数值,例如:
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:
invoice_number
**************
HKL1
HKL2
HKL3
.....
HKL12
HKL13
HKL14
HKL15
我尝试选择最大值,但返回的是"HKL9",而不是最大值"HKL15".
I tried to select max of it, but it returns with "HKL9", not the highest value "HKL15".
SELECT MAX( invoice_number )
FROM `invoice_header`
推荐答案
HKL9
(字符串)大于HKL15
,因为它们被比较为字符串.解决问题的一种方法是定义一个列函数,该函数仅返回发票编号的数字部分.
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.
如果所有发票编号均以HKL
开头,则可以使用:
If all your invoice numbers start with HKL
, then you can use:
SELECT MAX(CAST(SUBSTRING(invoice_number, 4, length(invoice_number)-3) AS UNSIGNED)) FROM table
它将发票编号(不包括前3个字符)转换为int,然后从中选择max.
It takes the invoice_number excluding the 3 first characters, converts to int, and selects max from it.
这篇关于如何选择最大的混合字符串/整数列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!