nuevo COUNT para cada registro de un select

[7xtr3am]

La estructura de mi bd y tablas y campos es muy compleja por eso solo pondré un ejemplo, además mi intención es aprender y no hacer que hagan las cosas por mi, me gustaría hacerlo yo solo que ya me lie y ya no me sale. No se me ocurre que mas. A ver si me pueden ayudar.

Bueno la cosa es que tengo una tabla con 900 registros los campos originales eran digamos: id, nombre, apellido, edad, género, especialidad. Digamos que se el agrego un campo matricula y cada registro se hiso update a: la primera letra del apellido concatenada con la cadena ‘001’. De modo que si hay 50 personas con la M en el apellido habrá 50 matriculas que digan M001.

La matricula La cual es de la siguiente forma: X999, donde X es la primera letra del apellido y 999 es un número del 001 y va aumentando de 1 en uno. De tal modo que si tengo a los siguientes registros, las matriculas SERIAN :

Pérez Juan P001
Mesa Luis M001
Cota José C001
Trejo Alberto T001
Muños Érica M002
Matías Federico M003
Rulfo miguel R001
Púnica melisa P002

Es decir, la numeración 001-999 comienza con cada letra. Trate de hacer el update con el siguiente código pero no me sale. Mi intención era que en el update en: “set a.matricula” se le asigne el total al primer registro (digamos que avía 50 con al letra M), al segundo como se supone que hay un M050 y 49 M001 se supone que se el agregaría M049. Al 3ero, como hay un M050, un M0949 y cuarenta y ocho M001 quedaría M048 y asi sucesivamente. Pero mi consulta SQL solo hace el COUNT una ves y a todos les pone 50.

La pregunta es: Hay alguna forma de hacer que el SELECT COUNT se haga de nuevo para cada registro?

Código:

update
  alumno a,
  (
                  SELECT
                    matricula
                  FROM
                    (
                        SELECT
                          id_alumno,
                          correo,
                          ap_paterno,
                          matricula,
                          count( matricula ) AS total
                        FROM
                          alumno
                        GROUP BY matricula
                        ORDER BY total DESC , matricula
                    )t1
                  WHERE
                    total >1
  )m
  set a.matricula=concat(
                          substr(a.matricula, 1, 4 ),
                          (case
                            when m.total<10  then '00'
                            when m.total<100 then '0'
                            else ''
                          end),
                          m.total

                      )

WHERE
  a.matricula = m.matricula

de antemano, gracias.

[nevergame]

desde mysql no puedes si desde php o asp o algo asi porke puedes guardar en variables los count y rescatarlos cuando lso necesites te recomiendo variables de session

[7xtr3am]

muchas gracias por tu respuesta, eso em temia que tendra que hacerlo desde el lenguaje de programacion o ponerle un limit o algo asi a mi query y ejecutarlo varias veces.

bueno creo que si se puede desde mysql, con uno (o un par) de ciclos y variables. pero queria hacerlo todo desde un update, desde una sentencia, para incrementar mis conocimientos.

bueno muchas gracias, +1

[jurena]

7xtr3am,
en mi opinión es como dices, en MySQL puede hacerse usando dos variables, una que contendrá la de la letra inicial y otra el número (creo que hay alguna consulta de gnzsoloyo que lo hace con dos variables) y pasando dos ciclos, luego pondrías un LPAD para rellenar con ceros el número, pero deberías controlar también no pasar de 999, claro; y, por otra parte, qué ocurriría si borras un registro, es decir, uno de los nombres que empiezan por M más adelante; ¿te importaría mantener un M099, aunque hubiera sólo 98 nombres con M inicial? Y por otro lado, si no es mucho preguntar, ¿para qué quieres ese M004?; va a ser algún primary key o sólo se trata de un ejercicio? Una función de programación te permitiría generarlo en cada consulta sin tener que almacenar nada, y por tanto no hacer ningún update.

[gnzsoloyo]

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:

SELECT 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:

SELECT @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:

1, 5, 8, 6, 15

¿Se entiende cómo funciona?