Как построить SQLite-запрос для GROUP ORDER?
У меня довольно интересная ситуация. У меня есть база данных SQLite, полная адресов и сообщений (адреса не уникальны, сообщения есть). Каждое сообщение также имеет связанную с ним дату. Я хочу, чтобы выбрать первый адрес сообщения, сообщение, дату и количество сообщений, связанных с адресом.
Итак, я подумал: "Я могу просто GROUP по адресу получить только одно сообщение на адрес, затем ЗАКАЗАТЬ их по дате, а также получить COUNT столбца адреса."
Я сделал это, и это работает... любопытно. Он выбирает правильный счет, выбирает только одно сообщение на адрес и заказывает его по дате, но не выбирает последнее сообщение для адреса. Он кажется произвольным.
В качестве примера у меня есть три сообщения (от самых ранних к последним): A, B, C из адреса Y и три сообщения D, E, F из адреса Z. Запрос может получать сообщения B и E, а затем сортировать их по Дата. Он должен получать сообщения C и F и сортировать их по дате.
Вот что я до сих пор:
// 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");
Мне кажется, что это может иметь отношение к предложению HAVING
, но я действительно не знаю. Я много использовал MySQL с PHP, но никогда не приходил в касание HAVING
раньше. Я попытался установить мое предложение HAVING
на "MAX(date)"
, но это не повлияло. Если я ставлю GROUP BY
предложение "address, date"
, то они будут отсортированы по дате, но, конечно, все они индивидуальны, а не сгруппированы (поскольку даты отличаются).
Поиски Google оказались бесплодными; такие запросы, как "андроидный sqlite-порядок перед группой" и "андроид-sqlite-группа по заказу", не дают никаких связанных результатов.
Как я могу выбрать последнее сообщение для каждого адреса без удаления моего предложения GROUP
(как COUNT()
полагается на это)? Мне нужны два запроса?
Изменить: на основе ответа @Adrian связал меня с комментариями, я придумал два запроса, которые оба дали то же самое результат; одна строка, в которой счетчик равен 7 (это общее количество адресов, а не сообщений на адрес), а указанный адрес не относится к последнему сообщению.
Два запроса:
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
);
Ответы
Ответ 1
SQLite имеет расширение, которое делает greatest-n-per-group проблемы намного проще:
Если вы используете агрегированные функции MAX()
или MIN()
, и если вы одновременно выбираете другие столбцы, не используя их в агрегатной функции или группируя их, тогда получаемые значения для этих столбцов гарантированно будут выходить той же записи, которая имеет максимальное/минимальное значение. (Это не разрешено в других диалектах SQL и было представлено в SQLite 3.7.11.)
Итак, для вашей проблемы вы можете использовать такой запрос:
SELECT *, COUNT(address) AS count, MAX(date)
FROM messages_database
GROUP BY address
Если у вас нет SQLite 3.7.11 (что, скорее всего, на большинстве версий Android) или с использованием другого механизма SQL, следующий запрос будет работать:
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
Ответ 2
Решил! В итоге я использовал комбинацию @CL. и методы, описанные в моем отредактированном сообщении (уточнено в этом ответе, отправленный @Adrian).
Поскольку я не хотел использовать 3 SELECT
(как описано в @CL. answer), я использовал ту же концепцию INNER JOIN
, что и в других утверждениях, сохраняя при этом свою методологию.
В результате получится следующее:
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
);
И он отлично работает!