首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >如何构造一个按顺序分组的SQLite查询?

如何构造一个按顺序分组的SQLite查询?
EN

Stack Overflow用户
提问于 2012-09-27 02:22:16
回答 2查看 40.3K关注 0票数 18

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

因此,我想,“我可以只按地址分组,以便每个地址只获得一条消息,然后按日期对这些消息进行排序,并获取地址列的计数。”

我这么做了,而且成功了..。差不多吧。它获取正确的计数,每个地址只获取一条消息,并按日期对它们进行排序--但它不会为地址选择最新的消息。这似乎是武断的。

例如,我有三条来自地址Y的消息A、B、C和三条来自地址Z的消息D、E、F。查询可以获取消息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条款有关,但我真的不知道。我在PHP中经常使用MySQL,但以前从未接触过HAVING。我尝试将我的HAVING子句设置为"MAX(date)",但是没有效果。如果我将GROUP BY子句设置为"address, date",那么它们将按日期排序,但当然它们都是单独的,而不是分组的(因为日期不同)。

谷歌搜索已经证明是徒劳的;像"android sqlite order yield group“和"android sqlite group by order”这样的查询都没有相关的结果。

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

编辑:基于the answer @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
);
EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2012-09-27 02:56:34

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

如果您没有安装SQL3.7.11(可能在大多数安卓版本上)或使用其他SQLite引擎,则可以执行以下查询:

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
票数 16
EN

Stack Overflow用户

发布于 2012-09-27 03:27:11

解决了!我最终使用了@CL.的方法和我在编辑后的帖子中概述的方法的组合(在@Adrian发布的this answer中阐明)。

因为我不想使用3个SELECT语句(正如@CL的答案所描述的那样),所以我使用了与其他语句相同的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
);

而且它工作得很完美!

票数 7
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/12608025

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档