La consulta para lograr ese encadenamiento es un poquitin compleja, pero nada más que por necesitar cierta claridad en la redacción.
Aquí la estoy haciendo en tres niveles como para que se entienda cómo se obtiene.
El punto esencial es que esta consulta genera una tabla en memoria, de modo que mi sugerencia es crear con ella una tabla temporal, la cual podrás usar luego para insertar este código dentro de la tabla, o simplemente para representarlo. Tu verás.
La idea es:
Código sql:
Ver originalSELECT alumno_id, CONCAT(CARACTER, LPAD(CONTARSI,3,'0'))
FROM (SELECT
alumno_id,
IF(@LETTER = LETRA , @CONTAR := @CONTAR+1, @CONTAR := 1) CONTARSI,
(@LETTER := LETRA) CARACTER
FROM
(SELECT
alumno_id,
LEFT(alumno_id,1) LETRA,
@LETTER := '',
@CONTAR:=0
FROM alumno
ORDER BY apellido_alumno)
T1)
T2;
La función LPAD se usa en este caso para convertir un entero rellenando los espacios a la izquierda con ceros hasta la longitud deseada.
La idea es que en cada registro se verifica que la letra que se separó inicialmente (es más simple separar la inicial en el inicio de las consultas) sea igual a la que se está recibiendo en la variable de usuario. Si no lo es, el contador se pone en 1 y si lo es se suma uno. LUEGO se procede a recargar la variable con el valor de letra de ese registro.
En el uso de las variables de usuario hay que recordar que cada invocación de asignación se ejecuta en el punto donde la variable aparece, y no al terminar la sentencia que la usa.
Para que sea claro, esto se ejecuta una sola vez, pero la variable, al terminar la sentencia vale diferente en cada segmento:
Código SQL:
Ver originalSELECT @A:=1, @A:=@A+4, @A:=@A+3, @A:=@A-2, @A:=@A+9
FROM (SELECT @A:=0) a
El resultado sería una tabla con estos valores:
¿Se entiende cómo funciona?