用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
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: 0mysql> 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>
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
SELECT CONCAT( SUBSTR(ID FROM 1 FOR 4), MAX( CAST(SUBSTR(ID FROM 5) AS UNSIGNED))) FROM `TABLEA`; 执行这个语句就行了
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
select max(subString_index(id,'_',-1)+0) a,
from
tableA最后一位转成int类型,然后取值。
+--------+
| id |
+--------+
| 1-0-11 |
+--------+
1 row in set (0.00 sec)mysql>
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: 0mysql> 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>
+------+
| a |
+------+
| 10 |
+------+
1 row in set (0.00 sec)只能截取出来,你应该存储一样的长度1-0-01
1-0-02
执行这个语句就行了