Ir al contenido principal

Enumerar filas en una consulta con MySQL

Supongamos que tenemos tablas con la estructura siguiente:
documentos (iddocumento, nombre_documento, url_original, idtipo_documento, idproyecto)
proyectos (idproyecto, nombre_proyecto, longitud, unidad_medida)
tipo_documentos (idtipo_documento, descripcion_tipo_documento)
Tenemos necesidad de hacer una consulta como la siguiente: "Enumerar todos los documentos en la base de datos agrupados por proyecto"

Parece fácil, excepto por el término "enumerar", aquí tienes un truquito para que logres enumerar tus consultas:
SELECT (@rownum:=@rownum+1) AS rownum, nombre_documento, descripcion_tipo_documento, nombre_proyecto
FROM  (SELECT @rownum:=0) r, documentos AS d INNER JOIN proyectos AS p ON d.idproyecto = p.idproyecto INNER JOIN tipo_documentos AS td ON d.idtipo_documento = td.idtipo_documento
Pero que tal si te piden que enumeres los proyectos con sus correspondientes documentos?. Teniendo lo anterior es un poco mas sencillo
SELECT IF(@fila=proyectos.idproyecto, @rownum:=@rownum, @rownum:=@rownum+1) AS rownum, @fila:=proyectos.idproyecto AS fila, nombre_documento, descripcion_tipo_documento, nombre_proyecto
FROM (SELECT @rownum:=0) r, (SELECT @fila:=0) v, documentos AS d INNER JOIN proyectos AS p ON d.idproyecto = p.idproyecto  INNER JOIN tipo_documentos AS td ON d.idtipo_documento =  td.idtipo_documento
ORDER BY idproyecto

Comentarios

  1. Gracias por tu aporte...
    me parece muy util tu articulo..
    Tengo una duda y talvez tu sepas la respuesta...

    Necesito obtener el siguiente resultado:

    Digamos que tengo la siguiente Tabla (id, valor) con los siguientes registros:

    id valor
    A 10
    B 11
    C 13
    D 4
    E 8

    necesito hacer un select que me de el siguiente resultado

    id valor valor_anterior
    A 10 0
    B 11 10
    C 13 11
    D 4 13
    E 8 4

    es decir que tome el valor del registro anterior...

    Crees que sea posible realizarla dentro de un select..... o de alguna otra manera..

    GRACIAS DE ANTEMANO

    ResponderEliminar
  2. Bueno es posible si la columna valor_anterior va antes de valor, mira el siguiente código.

    SELECT id, (@anterior) AS valor_anterior, (@anterior := descripcion
    ) AS valor
    FROM (SELECT @anterior := '') r, `tabla`

    ResponderEliminar
  3. Hola, no se si este tema siga activo, el aporte me ayudo mucho, pero ahora tengo otro problema, no se si me podrian ayudar, necesito ejecutar ese tipo de consultas con C# 2010, pero al hacerlo me salta un error, he comprobado que es por las @, alguien sabe como superar este inconveniente??, gracias de antemano

    ResponderEliminar
  4. La consulta es específicamente para mysql, probablemente estes intentando ejecutarla contra SQL Server y definitvamente no te va a funcionar.

    ResponderEliminar
  5. hola gracias por responder, estoy usando MySqlDataAdapter para ejecutar la consulta, la consulta la envio como una cadena de texto a una funcion que se encarga de hacer la conexion a la BD y ejecutar la consulta, si quito la parte de la columna enumerada, la consulta si la ejecuta, pero si lo vuelvo a poner no lo hace y esa misma consulta la pongo en el Query de MySQL y la ejecuta sin problemas, si alguien tiene alguna idea que me pueda ayudar se los agradeceria, o en todo caso alguien a probado ejecutar esa consulta con C# y no a tenido problemas??, de ser asi porfavor pegar el codigo de ejemplo, gracias de antemano

    ResponderEliminar
  6. Parece ser un problema con el adaptador, debes agregar lo siguiente a tus parámetros de conexión:
    Allow User Variables=True

    Así por ejemplo te quedaría: "Database=testdb;Data Source=localhost;User Id=root;Password=hello;Allow User Variables=True"

    Mas Info: MySql.Data.MySqlClient.MySqlException: Parameter ‘@id’ must be defined

    ResponderEliminar
  7. Gracias por tu respuesta, estabas en lo cierto, al colocar Allow User Variables=True en la cadena de conexion, con eso se soluciono el problema, gracias por tu ayuda.

    ResponderEliminar
  8. Gracias Juan Carlos, eso es lo que faltaba.

    ResponderEliminar
  9. hola, yo otra vez por aki, espero alguien me pueda resolver esta duda, sigo ejecutando la consulta de enumeración, pero me ocurre un problema, la variable @rownum se reinicia, vuelve a 1, antes de terminar de enumerar todas las filas de un mismo ID, he comprobado que la variable @fila en primera no inicia con el valor que le asigno en el select, ademas que para la ultima fila del mismo ID @fila toma el valor del ID anterior, lo que ocasiona que @rownum se reinicie, puede sonar un poco enredado, pero alguien sabe como solucionar este problema.

    Gracias de antemano por cualquier ayuda que me puedan brindar

    ResponderEliminar
  10. Hola jalcar,

    Sí como tu dices suena bastante enredado, pero intentaré adivinar un poco:
    Dices: la variable @rownum se reinicia, vuelve a 1, antes de terminar de enumerar todas las filas de un mismo ID. Estas seguro que estas ordenando la consulta por el id que quieres contar? Al final de la consulta hay un : ORDER BY idproyecto este dato es esencial.
    Dices: he comprobado que la variable @fila en primera no inicia con el valor que le asigno en el select, Debería, talvez si pones aquí mismo la consulta o en gist y la compartes, te pueda entender mejor.
    Saludos!

    ResponderEliminar
  11. hola juan carlos, de verdad gracias por interesarte y por responder rapidamente a mi consulta, bueno he de comenzar diciendo que acabo de solucionar el problema hace literalmente un minuto :), lo del ordenamiento si lo puse y eso no soluciono mi problema, lo solucione colocándole a mi consulta el comando straight_join, que como lei por ahi, obligaba a MySQL a tratar las tablas de los JOINs en el orden en que se establecían, esto hizo que mi variable @fila si tome el valor inicial y por tanto lo demás funcione normalmente.

    Lo que intentaba hacer es que a unos propietarios de vivienda se les muestre su deuda ordenada por años y enumerada, pero para la ultima deuda me volvia a 1, no se como agregar una imagen aki para mostrarte lo que me ocurría, pero lo importante es que ya se soluciono.

    gracias de todas formas por tu buena intension de ayudar, saludos y exitos.

    ResponderEliminar

Publicar un comentario

Entradas populares de este blog

Expresiones Regulares y pruebas en javascript

¿Qué es una expresión regular?
Una expresión regular es una cadena que contiene una combinación de caracteres normales y metacaracteres o metasecuencias especiales. Los caracteres normales coinciden por ellos mismos. Los metacaracteres y metasecuencias son caracteres o secuencias de caracteres que representan ideas como cantidad, posiciones o tipos de caracteres.
Regular Expression Pocket Reference 2nd Ed - Tony Stubblebine - O'Reilly

¿Para qué son útiles las expresiones regulares?
Las expresiones son especialmente útiles para validar información, por ejemplo en formularios de ingreso de datos. Por ejemplo para validar que se ingresó un número de teléfono, puedes usar la siguiente expresión regular.

/^([\+][0-9]{1,3}[ \.\-])?([\(]{1}[0-9]{2,6}[\)])?([0-9 \.\-\/]{3,20})?$/

Parecieran símbolos al azar, pero nada mas lejos de la realidad. Te muestro una tabla básica con los elementos usados para crear expresiones regulares.

Carácter Texto buscado ^Principio de entrada o línea.$Fin de e…

jQuery DataTables y CodeIgniter

Ajax Source Datatables permite configurar fácilmente el origen de datos de la tabla, para que esta sea generada dinámicamente desde el servidor, así que con CodeIgniter tendríamos el siguiente código

public function page(){ $data['pedidos'] = $this->pedidos_model->get_pedidos($this->input->post('iDisplayStart')); define('AJAX_REQUEST', 1);//truco para que en nginx no muestre el debug $TOTAL = $this->pedidos_model->total(); echo json_encode(array('aaData'=>$data['pedidos'], 'iTotalRecords'=>$TOTAL, 'iTotalDisplayRecords'=>$TOTAL, 'sEcho'=>$this->input->post('sEcho'))); }
Este método producirá algo parecido a esto:

{"iTotalRecords":83099,"iTotalDisplayRecords":83099,"sEcho":"2", "aaData":[{"Id":"85514","num":"86109",&q…