如何通过以构建一个SQLite查询来？ [英] How to construct a SQLite query to GROUP by ORDER?

问题描述

我有一个相当有意思的情况。我有一个SQLite数据库详细地址和信息（地址不是唯一的;消息）。每个消息还具有与之相关联的日期。我想要做的是选择的第一的邮件地址，邮件，日期和的多少邮件的与该地址相关联。

所以，我想，我可以用地址每个地址只能得到一个消息刚组，然后顺序执行的日期，以及获取地址栏的计数。

我这样做，和它的作品...有点儿。它获取正确的计数，获取每个地址只有一个消息，并把它们的订单按日期 - 但它并没有选择的最新的的消息的地址。这似乎是任意的。

作为一个例子，我有三条消息（最早到最晚）A，B，C从地址Y和三个消息D，E，女从地址Z的查询可能获取信息B和E，则的按日期排序。它的应的获取信息C和F，和排序的按日期。

下面是我到目前为止有：

  //扩展版本：
光标光标= db.query（
    / *从* /messages_database
    / *选择* /新的String [] {*，COUNT（地址）数}，
    / *其中* /空，
    / *其中的args * /空，
    / * GROUP BY * /地址，
    / *有* /空，
    / * ORDER BY * /日期DESC
）;

//或者，相同的$在一行C ​​$ C：
光标光标= db.query（messages_database，新的String [] {*，COUNT（地址）数}，NULL，NULL，地址，空，日期降序）;
 

我觉得这可能需要做的 HAVING 条款，但我真的不知道。我使用的MySQL有很多用PHP，但从来没有触及 HAVING 之前。我尝试设置我的 HAVING 子句MAX（日），但没有任何效果。如果我把我的 GROUP BY 子句是的地址，日期，那么它们按日期排序但是，当然，他们都是单独的，而不是分组（因为日期不同）。

谷歌搜索已被证明毫无结果;查询像的的android源码秩序组之前的和的的android源码按顺序的产量不相关的结果。

我怎么能选择的一个最新的的为不删除我的集团条款（如 COUNT每个地址信息（） 依靠这一点）？我需要两个查询？

修改：基于 @Adrian答案挂我的意见，我想出了其中两个产生的相同结果的两个查询;一排，其中，所述计数为7（它是地址的总数量，而不是消息的每的地址），和所示的地址不是该最新消息的

这两个查询是：

 光标光标= db.rawQuery（
      选择T *，COUNT（t.message_content）AS计
    +FROM messages_database T
    +INNER JOIN（
    +选择地址，MAX（日期）AS的maxDate
    +FROM messages_database
    +GROUP BY地址
    +）分类ON t.address = ss.address与t.date = ss.maxdate，
    空值
）;
光标光标= db.rawQuery（
      选择T1 *，COUNT（t1.message_content）AS计
    +FROM messages_database T1
    +LEFT OUTER JOIN messages_database T2
    +开（t1.address = t2.address与t1.date＆所述; t2.date）
    +WHERE t2.address IS NULL
    空值
）;
 

解决方案

SQLite的拥有，使得扩展的最大正每组的问题要容易得多：
如果您使用的是 MAX（）或 MIN（）聚合函数，如果您选择其他列在不使用它们在聚合函数或由它们分组同一时间，那么这些列的结果值，保证出来的具有最大/最小值相同的记录。 （这是不是在其他SQL方言允许的，并且在的SQLite 3.7.11 的介绍。）

因此​​，对于您的问题，您可以使用查询是这样的：

  SELECT *，COUNT（地址）数，MAX（日）
从messages_database
GROUP BY地址
 

---

如果您还没有的SQLite 3.7.11（这是很可能在大多数Android版本），或使用其它SQL引擎，下面的查询将工作：

  SELECT *，
       （SELECT COUNT（地址）数
        从messages_database平方米
        WHERE m1.address = m2.address）
从messages_database M1
其中date =（SELECT MAX（日）
              从messages_database立方米
              WHERE m1.address = m3.address）
GROUP BY地址
 

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 first message's address, message, date, and how many messages are associated with the address.

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."

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 recent message 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, then sort them by date. It should fetch messages C and F, and sort those by date.

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 HAVING clause, but I really don't know. I've used MySQL a lot with PHP, but never had to touch HAVING before. I tried setting my HAVING clause to "MAX(date)", but it had no effect. If I set my GROUP BY clause to be "address, date", then they are sorted by date, but of course they are all individual instead of grouped (since the dates differ).

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

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

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 per address), and the address shown was not that of the latest message.

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

解决方案

SQLite has an extension that makes greatest-n-per-group problems 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.)

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:

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

这篇关于如何通过以构建一个SQLite查询来？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！