MySQL 如何获取字符串中的最大值

您可以使用MAX()和CAST()来实现此功能。由于该字符串中充满了字符串和整数，例如“STU201”，因此我们需要使用CAST()。

首先让我们创建一个表 –

mysql> create table DemoTable
(
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   StudentBookCode varchar(200)
);
Query OK, 0 rows affected (0.56 sec)

使用插入命令向表中插入一些记录 –

mysql> insert into DemoTable(StudentBookCode) values('STU201');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable(StudentBookCode) values('STU202');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable(StudentBookCode) values('STU203');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable(StudentBookCode) values('STU290');
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable(StudentBookCode) values('STU234');
Query OK, 1 row affected (0.15 sec)

以下是使用select语句从表中显示所有记录的查询 –

mysql> select *from DemoTable;

这将产生以下输出 –

+----+-----------------+
| Id | StudentBookCode |
+----+-----------------+
| 1  | STU201          |
| 2  | STU202          |
| 3  | STU203          |
| 4  | STU290          |
| 5  | STU234          |
+----+-----------------+
5 rows in set (0.00 sec)

以下是获取最大值的查询语句 –

mysql> select MAX(CAST(SUBSTRING(StudentBookCode FROM 4) AS UNSIGNED)) from DemoTable;

这将产生以下输出 –

+----------------------------------------------------------+
| MAX(CAST(SUBSTRING(StudentBookCode FROM 4) AS UNSIGNED)) |
+----------------------------------------------------------+
| 290                                                      |
+----------------------------------------------------------+
1 row in set (0.00 sec)

阅读更多：MySQL 教程