我有一个相当有趣的情况。我有一个充满地址和消息的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
);
发布于 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
发布于 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
);
而且它工作得很完美!
https://stackoverflow.com/questions/12608025
复制相似问题