MySQL只计算新记录

Question

如何编写 MySQL 查询来完成此任务？

表:作者

w_id    w_name
---------------
  1     Michael
  2     Samantha
  3     John
---------------

表:文章

a_id   w_id   timestamp   a_name
----------------------------------------
  1      1        1       PHP programming
  2      3        3       Other programming languages
  3      3        5       Another article
  4      2       15       Web design
  5      1       20       MySQL
----------------------------------------

只需要COUNT作者发表第一篇文章的时间不早于5。
(至少发表过一篇文章的作者才算在内)

在此示例中，结果将是:1 (一位作家 - Samantha)

SQL代码可以测试in this SQLFiddle

Answer 1

你需要一个双 SELECT。

首先获取w_id，连同第一篇文章的时间戳:

SELECT w_id, MIN(timestamp) as publish FROM articles GROUP BY w_id

然后你要求publish不早于5:

SELECT w_id, MIN(timestamp) as publish
    FROM articles
    GROUP BY w_id
    HAVING publish >= 5;

然后，如果需要，您可以加入包含 writers 的“表格”以获取名称。

但是如果你只想要计数，你根本不需要 writers 表:

    SELECT COUNT(*) AS answer FROM
    ( SELECT w_id, MIN(timestamp) AS publish
        FROM articles
        GROUP BY w_id
        HAVING publish >= 5
) AS counter;

测试:http://sqlfiddle.com/#!2/0e90f/30/0