Cómo construir una consulta SQLite a GROUP by ORDER?
Tengo una situación bastante interesante. Tengo una base de datos SQLite llena de direcciones y mensajes (las direcciones no son únicas, los mensajes son). Cada mensaje también tiene una fecha asociada con él. Lo que quiero hacer es seleccionar la dirección del primer mensaje, el mensaje, la fecha y cuántos mensajes están asociados con la dirección.
Por lo tanto, pensé: "Sólo puedo GRUPO por la dirección de sólo obtener un mensaje por dirección, a continuación, ORDEN estos por la fecha, y también buscar la COUNT columna de la dirección."
- Identificar el tipo de datos de una columna en un Cursor Android de SQLite
- DB SQLite de Android Cuando cerrar
- ¿Cómo deshabilitar el archivo de diario SQLite de SQL?
- ¿Cómo funciona un cursor SQLite trabajar internamente?
- Android: copia la base de datos de la carpeta de activos, pero sólo obtiene un archivo vacío
Lo hice, y funciona … un poco. Obtiene el recuento correcto, obtiene sólo un mensaje por dirección y los ordena por fecha, pero no selecciona el mensaje más reciente para la dirección. Parece ser arbitrario.
Como ejemplo, tengo tres mensajes (de la más temprana a la última) A, B, C de la dirección Y, y tres mensajes D, E, F de la dirección Z. La consulta puede recuperar los mensajes B y E, luego ordenarlos por fecha. Debe buscar los mensajes C y F, y ordenar los por fecha.
Esto es lo que tengo hasta ahora:
// Expanded version: Cursor cursor = db.query( /* FROM */ "messages_database", /* SELECT */ new String[]{ "*", "COUNT(address) AS count" }, /* WHERE */ null, /* WHERE args */ null, /* GROUP BY */ "address", /* HAVING */ null, /* ORDER BY */ "date DESC" ); // Or, same code on one line: Cursor cursor = db.query("messages_database", new String[]{ "*", "COUNT(address) AS count" }, null, null, "address", null, "date DESC");
Siento que esto puede tener que ver con la cláusula HAVING
, pero realmente no sé. He utilizado MySQL mucho con PHP, pero nunca tuvo que tocar HAVING
antes. Traté de establecer mi cláusula HAVING
a "MAX(date)"
, pero no tuvo ningún efecto. Si establezco mi cláusula GROUP BY
como "address, date"
, se ordenan por fecha, pero por supuesto son todos individuales en lugar de agrupados (ya que las fechas son diferentes).
Las búsquedas de Google han resultado infructuosas; Consultas como " androide sqlite orden antes grupo " y " androide sqlite grupo por pedido " no producen resultados relacionados.
¿Cómo puedo seleccionar el último mensaje para cada dirección sin eliminar mi cláusula GROUP
(como COUNT()
basa en esto)? ¿Necesito dos consultas?
Edit : Basado en la respuesta @Adrian me vinculó a en los comentarios, me surgió con dos preguntas que tanto produjo el mismo resultado, Una fila, en la que el recuento era 7 (que es el número total de direcciones, no mensajes por dirección), y la dirección mostrada no era la del último mensaje.
Las dos preguntas fueron:
Cursor cursor = db.rawQuery( "SELECT t.*, COUNT(t.message_content) AS count " + "FROM messages_database t " + "INNER JOIN (" + " SELECT address, MAX(date) AS maxdate " + " FROM messages_database " + " GROUP BY address " + ") ss ON t.address = ss.address AND t.date = ss.maxdate", null ); Cursor cursor = db.rawQuery( "SELECT t1.*, COUNT(t1.message_content) AS count " + "FROM messages_database t1 " + "LEFT OUTER JOIN messages_database t2 " + "ON (t1.address = t2.address AND t1.date < t2.date) " + "WHERE t2.address IS NULL", null );
- Android Lollipop - comportamiento cambiado de SQLite
- La aplicación no cerró el cursor o el objeto de base de datos que se abrió aquí:
- Identificador único para SMS y contactos (para comprobar la duplicación) en Android
- Obtener información de la base de datos sqlite
- ¿Es posible obtener la última fecha de modificación de un archivo de activos?
- Error log sqlite no se ha podido cargar desde /cachedGeplacement.db
- ¿Hay alguna forma más rápida de iterar a través de las filas de la consulta Sqlite?
- No se copia la base de datos de la carpeta de activos al dispositivo
SQLite tiene una extensión que hace que los problemas de mayor n-por-grupo sean mucho más fáciles:
Si está utilizando las funciones de agregado MAX()
o MIN()
y si está seleccionando otras columnas al mismo tiempo sin utilizarlas en una función agregada o agrupación por ellas, los valores resultantes para esas columnas se garantizan que saldrán Del mismo registro que tiene el valor máximo / mínimo. (Esto no se permite en otros dialectos SQL, y se introdujo en SQLite 3.7.11 .)
Por lo tanto, para su problema, puede utilizar una consulta como esta:
SELECT *, COUNT(address) AS count, MAX(date) FROM messages_database GROUP BY address
Si no tiene SQLite 3.7.11 (que es probable en la mayoría de las versiones de Android) o utilizando otro motor de SQL, la siguiente consulta funcionará:
SELECT *, (SELECT COUNT(address) AS count FROM messages_database m2 WHERE m1.address = m2.address) FROM messages_database m1 WHERE date = (SELECT MAX(date) FROM messages_database m3 WHERE m1.address = m3.address) GROUP BY address
¡Resuelto! Terminé usando una combinación de @CL. Y los métodos que describí en mi post editado (aclarado en esta respuesta , publicado por @Adrian ).
Como no quería usar 3 SELECT
(como la respuesta de @ CL.), INNER JOIN
el mismo concepto INNER JOIN
como en las otras sentencias, manteniendo su metodología.
El resultado es el siguiente:
Cursor cursor = db.rawQuery( "SELECT t.*, ss.count AS count " + "FROM messages_database t " + "INNER JOIN (" + " SELECT address, MAX(date) AS maxdate, COUNT(address) AS count " + " FROM messages_database " + " GROUP BY address " + ") ss ON t.address = ss.address AND t.date = ss.maxdate " + "GROUP BY t.address " + "ORDER BY t.date DESC ", null );
¡Y funciona perfectamente!
- L-release-like Touch Ripple animación en pre-L
- Glide: ¿Cómo encontrar si la imagen ya está en caché y utilizar la versión en caché?