如何在MySQL中选取字符串/整数混合列的最大值？

要选取字符串/整数混合列的最大值，您需要使用substring（）函数，语法如下：

SELECT MAX(CAST(SUBSTRING(yourColumnName, 4, length(yourColumnName)-3) AS UNSIGNED)) AS anyVariableName FROM yourTableName;

为了理解上述的语法，让我们创建一个表。创建表的查询语句如下：

mysql> create table StringIntMixHighestDemo
   -> (
   -> InvoiceId int NOT NULL AUTO_INCREMENT,
   -> InvoiceNumber varchar(20),
   -> PRIMARY KEY(InvoiceId)
   -> );
Query OK, 0 rows affected (0.65 sec)

现在您可以使用insert命令在表中插入一些记录，查询语句如下：

mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV129');
Query OK, 1 row affected (0.11 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV122');
Query OK, 1 row affected (0.22 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV1');
Query OK, 1 row affected (0.15 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV145');
Query OK, 1 row affected (0.18 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV19');
Query OK, 1 row affected (0.10 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV134');
Query OK, 1 row affected (0.13 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV135');
Query OK, 1 row affected (0.16 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV10');
Query OK, 1 row affected (0.14 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV100');
Query OK, 1 row affected (0.11 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV121');
Query OK, 1 row affected (0.27 sec)

使用select语句显示表中的所有记录，查询语句如下：

mysql> select *from StringIntMixHighestDemo;

以下是输出结果：

+-----------+---------------+
| InvoiceId | InvoiceNumber |
+-----------+---------------+
|         1 | INV129        |
|         2 | INV122        |
|         3 | INV1          |
|         4 | INV145        |
|         5 | INV19         |
|         6 | INV134        |
|         7 | INV135        | 
|         8 | INV10         |
|         9 | INV100        |
|        10 | INV121        |
+-----------+---------------+
10 rows in set (0.00 sec)

以下是获取字符串/整数列中最大值的查询语句：

mysql> SELECT MAX(CAST(SUBSTRING(InvoiceNumber, 4, length(InvoiceNumber)-3) AS UNSIGNED)) as HighestValue
   -> from StringIntMixHighestDemo;

以下是输出结果：

+--------------+
| HighestValue |
+--------------+
|          145 |
+--------------+
1 row in set (0.00 sec)

阅读更多：MySQL 教程