Android 如何通过 ORDER 构建对 GROUP 的 SQLite 查询？

How to construct a SQLite query to GROUP by ORDER?

提问by Eric

I've got a rather interesting situation. I have a SQLite database full of addresses and messages (addresses are not unique; messages are). Each message also has a date associated with it. What I want to do is select the firstmessage's address, message, date, and how many messagesare associated with the address.

我有一个相当有趣的情况。我有一个充满地址和消息的 SQLite 数据库（地址不是唯一的；消息是）。每条消息还有一个与之关联的日期。我想要做的是选择第一条消息的地址、消息、日期以及与该地址关联的消息数量。

So, I thought, "I can just GROUP by the address to only get one message per address, then ORDER these by the date, and also fetch the COUNT of the address column."

所以，我想，“我可以按地址分组，每个地址只能获取一条消息，然后按日期对这些消息进行排序，并获取地址列的 COUNT。”

I did that, and it works... kinda. It fetches the correct count, fetches only one message per address, and orders them by date--but it does not select the most recentmessage for the address. It appears to be arbitrary.

我这样做了，它有效......有点。它获取正确的计数，每个地址只获取一条消息，并按日期对其进行排序——但它不会为地址选择最新的消息。这似乎是任意的。

As an example, I have three messages (earliest to latest) A, B, C from address Y, and three messages D, E, F from address Z. The query may fetch messages B and E, thensort them by date. It shouldfetch messages C and F, and sort those by date.

例如，我有来自地址 Y 的三个消息（从最早到最新）A、B、C，以及来自地址 Z 的三个消息 D、E、F。查询可以获取消息 B 和 E，然后按日期对它们进行排序。它应该获取消息 C 和 F，并按日期对它们进行排序。

Here is what I have so far:

这是我到目前为止所拥有的：

// 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");

I feel like this may have to do with the HAVINGclause, but I really don't know. I've used MySQL a lot with PHP, but never had to touch HAVINGbefore. I tried setting my HAVINGclause to "MAX(date)", but it had no effect. If I set my GROUP BYclause to be "address, date", then they are sorted by date, but of course they are all individual instead of grouped (since the dates differ).

我觉得这可能与HAVING条款有关，但我真的不知道。我在 PHP 中经常使用 MySQL，但以前从未接触过HAVING。我尝试将我的HAVING子句设置为"MAX(date)"，但没有效果。如果我将我的GROUP BY子句设置为"address, date"，则它们按日期排序，但当然它们都是单独的而不是分组的（因为日期不同）。

Google searches have proved fruitless; queries like "android sqlite order before group" and "android sqlite group by order" yield no related results.

事实证明，谷歌搜索毫无结果；诸如“ android sqlite order before group”和“ android sqlite group by order”之类的查询不会产生相关结果。

How can I select the one latestmessage for each address without removing my GROUPclause (as COUNT()relies upon this)? Do I need two queries?

如何在不删除我的子句的情况下为每个地址选择一条最新消息GROUP（COUNT()依赖于此）？我需要两个查询吗？

Edit: Based on the answer@Adrian linked me to in the comments, I came up with two queries which both produced the same result; one row, in which the count was 7 (which is the total number of addresses, not messages peraddress), and the address shown was not that of the latest message.

编辑：根据@Adrian 在评论中链接我的答案，我提出了两个产生相同结果的查询；一行，其中计数为 7（这是地址总数，不是每个地址的消息），并且显示的地址不是最新消息的地址。

The two queries were:

这两个查询是：

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
);

采纳答案by CL.

SQLite has an extension that makes greatest-n-per-groupproblems much easier:
If you are using the MAX()or MIN()aggregate functions, and if you are selecting other columns at the same time without using them in an aggregate function or grouping by them, then the resulting values for those columns are guaranteed to come out of the same record that has the maximum/minimum value. (This is not allowed in other SQL dialects, and was introduced in SQLite 3.7.11.)

SQLite 有一个扩展，可以使每组最大 n 个问题更容易：
如果您使用MAX()或MIN()聚合函数，并且如果您同时选择其他列而不在聚合函数中使用它们或按它们分组，那么这些列的结果值保证来自具有最大值/最小值的同一记录。（这在其他 SQL 方言中是不允许的，并在SQLite 3.7.11中引入。）

So, for your problem, you can use a query like this:

因此，对于您的问题，您可以使用如下查询：

SELECT *, COUNT(address) AS count, MAX(date)
FROM messages_database
GROUP BY address

---

---

If you don't have SQLite 3.7.11 (which is likely on most Android versions) or using another SQL engine, the following query will work:

如果您没有 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

回答by Eric

Solved it! I ended up using a combination of @CL.'s method and the methods I outlined in my edited post (clarified in this answer, posted by @Adrian).

解决了！我最终使用了@CL的组合。的方法和我在编辑过的帖子中概述的方法（在这个答案中澄清，由@Adrian发布）。

Because I didn't want to use 3 SELECTstatements (as @CL.'s answer described), I used the same INNER JOINconcept as in the other statements, while retaining his methodology.

因为我不想使用 3 个SELECT语句（如@CL. 的回答所述），所以我使用了与INNER JOIN其他语句中相同的概念，同时保留了他的方法论。

The result is this:

结果是这样的：

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
);

And it's working perfectly!

它运行良好！