求最大值的sql

[lcllcl987]

我有一个tableA， 里面有一个字段id，是字符型的，数据如下：
ID
1-0-1
1-0-2
1-0-3
1-0-4
1-0-5
1-0-6
1-0-7
1-0-8
1-0-9
1-0-10
1-0-11
我想取里面的最大值，也就是1-0-11
但是用max()函数取出来的居然是1-0-9
select max(id) from tableA
有什么好办法能一下取到我想要的结果：
1-0-11
？

[搬砖的IT农民工]

select max(subString_index(id,'_',-1)+0) a,
from 
tableA

最后一位转成int类型，然后取值。

[九月茅]

mysql> SELECT a.id FROM A a ORDER BY (REPLACE(a.id,'-','')+0) DESC LIMIT 1;
+--------+
| id     |
+--------+
| 1-0-11 |
+--------+
1 row in set (0.00 sec)

mysql>

[九月茅]

mysql> CREATE TABLE test.A (id VARCHAR(100));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO
    -> test.A 
    -> SELECT 
    -> '1-0-1' AS id  UNION ALL SELECT
    -> '1-0-2'UNION ALL SELECT
    -> '1-0-3'UNION ALL SELECT
    -> '1-0-4'UNION ALL SELECT
    -> '1-0-5'UNION ALL SELECT
    -> '1-0-6'UNION ALL SELECT
    -> '1-0-7'UNION ALL SELECT
    -> '1-0-8'UNION ALL SELECT
    -> '1-0-9'UNION ALL SELECT
    -> '1-0-10'UNION ALL SELECT
    -> '1-0-11';
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> SELECT a.id FROM A a ORDER BY (REPLACE(a.id,'-','')+0) DESC LIMIT 1;
+--------+
| id     |
+--------+
| 1-0-11 |
+--------+
1 row in set (0.01 sec)

mysql> 

[SQL77]

mysql> SELECT REVERSE(SUBSTRING(REVERSE('1-0-10'),1,LOCATE('-',REVERSE('1-0-10'))-1)) as a ; +------+ | a | +------+ | 10 | +------+ 1 row in set (0.00 sec) 只能截取出来，你应该存储一样的长度1-0-01 1-0-02

[赵增光]

用order by + limit 解决问题：

select
    substring_index(id,'-',1) a,
    substring_index(substring_index(id,'-',-2),'-',1) b,
    substring_index(id,'-',-1) c
from
    tableA
order by a,b,c
limit 0,1

[知道就是你]

呵呵 如果 是经常使用的话 ，建议创建一个int类型的字段，单独保存，在赋值时，可以参照楼上的那种方式，效率可能高一点！

[馨悦传奇]

SELECT CONCAT( SUBSTR(ID FROM 1 FOR 4), MAX( CAST(SUBSTR(ID FROM 5) AS UNSIGNED))) FROM `TABLEA`; 执行这个语句就行了

[wwwwb]

把1－0－9变为－＞1－0－09即可取MAX