我面临用01、02,…,10、12递增字符串的问题
>我有一个SQL查询,该查询给了我AAAA06,这在我的数据库中最大.
>现在,我有一个要求,每当用户调用此命令时,它将使用最大编号检查数据库.当前为AAAA06(当前为最大值),应返回或打印AAAA07.
>同样,当最大值为AAAA09时,则应增加为AAAA10,依此类推
>作为一种语言,我正在使用Java,但是我想知道我可以通过自己的SQL查询来实现此目的还是必须编写Java代码来实现此目的？
我正在使用的查询就像
select max(code) from mt_users where maincode='AAAA'
叫最大否是我做的.我只想增加或连接适合获得期望输出的任何内容.
它返回我AAAA06,因为这是当前的最大值.
重要事项
>最初为AAAA时,应显示AAAA01
>如果是AAAA09,则应显示AAAA10
>当它是AAAA99时将显示AAAA100
注意：当前我的最大身份为AAAA06,但是对于新用户而言,将来可能是AAAE,因此我必须以AAAE01开头.
最佳答案
简短答案-使用此查询：
SELECT id AS PrevID,CONCAT(
SUBSTRING(id,1,4),IF(CAST(SUBSTRING(id,5) AS UNSIGNED) <= 9,'0',''),CAST(SUBSTRING(id,5) AS UNSIGNED) + 1
) AS NextID
FROM (
-- since you allow strings such as AAAA20 and AAAA100 you can no longer use MAX
SELECT id
FROM t
ORDER BY SUBSTRING(id,4) DESC,5) AS UNSIGNED) DESC
LIMIT 1
) x
结果：
| PrevID | NextID |
| AAAA100 | AAAA101 |
| AAAA21 | AAAA22 |
| AAAA06 | AAAA07 |
只是为了好玩,我写了这个存储过程,它生成的数字看起来像AAAA00 AAAA99 AAAB00等：
CREATE FUNCTION NextID(PrevID VARCHAR(6))
RETURNS VARCHAR(6)
BEGIN
DECLARE s VARCHAR(4);
DECLARE i INT;
DECLARE j INT;
SET s = LEFT(PrevID,4);
SET s = REPLACE(s,'A','0');
SET s = REPLACE(s,'B','1');
SET s = REPLACE(s,'C','2');
SET s = REPLACE(s,'D','3');
SET s = REPLACE(s,'E','4');
SET s = REPLACE(s,'F','5');
SET s = REPLACE(s,'G','6');
SET s = REPLACE(s,'H','7');
SET s = REPLACE(s,'I','8');
SET s = REPLACE(s,'J','9');
SET s = REPLACE(s,'K','A');
SET s = REPLACE(s,'L','B');
SET s = REPLACE(s,'M','C');
SET s = REPLACE(s,'N','D');
SET s = REPLACE(s,'O','E');
SET s = REPLACE(s,'P','F');
SET s = REPLACE(s,'Q','G');
SET s = REPLACE(s,'R','H');
SET s = REPLACE(s,'S','I');
SET s = REPLACE(s,'T','J');
SET s = REPLACE(s,'U','K');
SET s = REPLACE(s,'V','L');
SET s = REPLACE(s,'W','M');
SET s = REPLACE(s,'X','N');
SET s = REPLACE(s,'Y','O');
SET s = REPLACE(s,'Z','P');
SET i = RIGHT(PrevID,2);
SET j = CONV(s,26,10);
SET i = i + 1;
IF i > 99 THEN
SET i = 0;
SET j = j + 1;
END IF;
SET s = CONV(j,10,26);
SET s = REPLACE(s,'Z');
SET s = REPLACE(s,'Y');
SET s = REPLACE(s,'X');
SET s = REPLACE(s,'W');
SET s = REPLACE(s,'V');
SET s = REPLACE(s,'U');
SET s = REPLACE(s,'T');
SET s = REPLACE(s,'S');
SET s = REPLACE(s,'R');
SET s = REPLACE(s,'Q');
SET s = REPLACE(s,'P');
SET s = REPLACE(s,'9','8','7','6','5','4','3','2','1','A');
RETURN CONCAT(LPAD(s,4,'A'),LPAD(i,2,'0'));
END
SELECT NextID('AAAA01') -- AAAA02
SELECT NextID('AAAA99') -- AAAB00
SELECT NextID('AAAB99') -- AAAC00
SELECT NextID('AAAZ99') -- AABA00