SQLite:不同表上的两个不同计数

Question

我非常肯定你们有一个简单而快速的解决方案来解决我的问题，但是我的SQL技能有限，我自己也搞不清楚。

所以我得到的是这样的：

新闻组:名称(主键)

文章:Id(主键)日期:日期

我想知道的是一个查询，它给我每个新闻组的名称，以及未读文章的计数，所有文章的计数和最近一篇文章的日期……这是否有可能在单个Select-Query中实现？

提前感谢你们！

Answer 1

您可以简单地使用适当的聚合函数：

SELECT newsgroupName,
       SUM(NOT read) AS countUnread,
       COUNT(*)      AS countAll,
       MAX(date)     AS mostRecentDate
FROM Article
GROUP BY newsgroupName;

Answer 2

我猜大概是这样的：

SELECT name, COUNT(countUnread) AS countUnread, COUNT(countRead) AS countRead, MAX(mostRecentDateUnread) AS mostRecentDateUnread, MAX(mostRecentDateRead) AS mostRecentDateRead FROM (
    SELECT newsgroupName AS name, COUNT(newsgroupName) AS countUnread, 0 AS countRead, MAX(date) AS mostRecentDateUnread, NULL AS mostRecentDateRead
    FROM Article
    WHERE read = 0
    GROUP BY newsgroupName, read

    UNION ALL

    SELECT newsgroupName AS name, 0 AS countUnread, COUNT(newsgroupName) AS countRead, NULL AS mostRecentDateUnread, MAX(date) AS mostRecentDateRead
    FROM Article
    WHERE read = 1
    GROUP BY newsgroupName, read
)
GROUP BY name

我没有尝试过，但理论上通过一些修复，它可以工作。