增加LSB1,字段ID,自增,内容1-100000 SELECT ss,0+GROUP_CONCAT(newss ORDER BY id SEPARATOR '' ) FROM ( SELECT *,MID(ss,id,1) AS newss FROM (SELECT 'abcdef1001235456789' AS ss UNION SELECT 'rm102' ) a LEFT JOIN zz.lsb1 b ON LENGTH(a.ss)>=b.id WHERE 0+MID(ss,id,1)>0 OR (0+(MID(ss,id,1))=0 AND MID(ss,id,1)='0')) f GROUP BY ss ORDER BY 2 ; 将SELECT 'abcdef1001235456789' AS ss UNION SELECT 'rm102'修改为你的字段名即可
有一个一条语句的方法: 就是有点不好看。 mysql> select * from td; +----------------+ | id | +----------------+ | abcdef12341414 | +----------------+ 1 row in set (0.00 sec)mysql> insert into td values('ab23455'); Query OK, 1 row affected (0.03 sec)mysql> select max(cast(reverse(cast(cast(reverse(id) as decimal) as char)) as decimal)) from td; +---------------------------------------------------------------------------+ | max(cast(reverse(cast(cast(reverse(id) as decimal) as char)) as decimal)) | +---------------------------------------------------------------------------+ | 12341414 | +---------------------------------------------------------------------------+ 1 row in set (0.06 sec)原理就是两次反转,利用cast只看前头的数字的特点。
select max(right(name,3)) from tt
另外比如 mo12test1234 这种情况,它的数字是多少?
abcdef123456789
存储过程,怎么写?
增加LSB1,字段ID,自增,内容1-100000
SELECT ss,0+GROUP_CONCAT(newss ORDER BY id SEPARATOR '' ) FROM (
SELECT *,MID(ss,id,1) AS newss FROM (SELECT 'abcdef1001235456789' AS ss
UNION
SELECT 'rm102' ) a
LEFT JOIN zz.lsb1 b ON LENGTH(a.ss)>=b.id
WHERE 0+MID(ss,id,1)>0 OR (0+(MID(ss,id,1))=0 AND MID(ss,id,1)='0')) f
GROUP BY ss ORDER BY 2
;
将SELECT 'abcdef1001235456789' AS ss
UNION
SELECT 'rm102'修改为你的字段名即可
就是有点不好看。
mysql> select * from td;
+----------------+
| id |
+----------------+
| abcdef12341414 |
+----------------+
1 row in set (0.00 sec)mysql> insert into td values('ab23455');
Query OK, 1 row affected (0.03 sec)mysql> select max(cast(reverse(cast(cast(reverse(id) as decimal) as char)) as decimal)) from td;
+---------------------------------------------------------------------------+
| max(cast(reverse(cast(cast(reverse(id) as decimal) as char)) as decimal)) |
+---------------------------------------------------------------------------+
| 12341414 |
+---------------------------------------------------------------------------+
1 row in set (0.06 sec)原理就是两次反转,利用cast只看前头的数字的特点。