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

问题描述：

让我们说我有一个表，其中包含发票编号列，数据类型为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.

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

相关推荐