如何使用字符串和Int值选择最大值

Question

嗨，我有这张表..

offer_number      training_title
**************
    ABC-1          SEMINAR
    ABC-9          SEMINAR
    ABC-10         SEMINAR
    ABCD-9         TRAINING
    EFGH-9         TESTING
    EFGH-10        TESTING

Mysql

SELECT *, MAX(offer_number) as offer_number_latest FROM (`training_program`) WHERE `training_title` LIKE '%SEMINAR%' GROUP BY `training_title` ORDER BY `offer_number` desc

我想生产ABC-10，但我总是得到ABC-9，而不是ABC-10。

示例2：

 SELECT *, MAX(offer_number) as offer_number_latest FROM (`training_program`) WHERE `training_title` LIKE '%TESTING%' GROUP BY `training_title` ORDER BY `offer_number` desc  

我需要一个结果EFGH-10，但我总是得到EFGH-9而不是EFGH-10。

Answer 1

如果每个training_title的offer_number前缀始终相同，则将执行以下(讨厌)查询：

select training_title, 
  concat(
    left(offer_number, 
      locate('-', offer_number)
    ), 
    max(
      cast(substring(offer_number, locate('-', offer_number) + 1) as signed)
    )
  ) from offers group by training_title

demo here

Answer 2

Mysql也有SUBSTRING功能。

 mysql> select * from offer;
    +--------------+----------------+
    | offer_number | training_title |
    +--------------+----------------+
    | ABC-1        | SEMINAR        |
    | ABC-9        | SEMINAR        |
    | ABC-10       | SEMINAR        |
    | ABC-8        | SEMINAR        |
    | ABC-14       | SEMINAR        |
    | ABC-12       | SEMINAR        |
    +--------------+----------------+
    6 rows in set (0.00 sec)

    mysql> SELECT MAX(CAST(SUBSTRING(offer_number,LOCATE('-',offer_number)+1) AS SIGNED)) as MAX FROM offer ;
    +------+
    | MAX  |
    +------+
    |   14 |
    +------+
    1 row in set (0.00 sec)

    mysql> SELECT * FROM offer ORDER BY CAST(SUBSTRING(offer_number,LOCATE('-',offer_number)+1) AS SIGNED);
    +--------------+----------------+
    | offer_number | training_title |
    +--------------+----------------+
    | ABC-1        | SEMINAR        |
    | ABC-8        | SEMINAR        |
    | ABC-9        | SEMINAR        |
    | ABC-10       | SEMINAR        |
    | ABC-12       | SEMINAR        |
    | ABC-14       | SEMINAR        |
    +--------------+----------------+
    6 rows in set (0.00 sec)