MySQL 查询 GROUP BY 两列

Question

我需要 MySQL 查询方面的帮助
我有这个百合:

表A
- page_id
- rev_id
- 日期
一个 page_id 可以有多个 rev_id

表B
- rev_id
- 单词
我有每个修订版中的单词

我需要为每个日期返回我在该日期中拥有的单词数量每个 page_id 中的最后一个 rev_id

示例:

table A
page_id | rev_id   |   date
---------------------------------
 1      |  231     |   2002-01-01
 2      |  345     |   2002-10-12
 1      |  324     |   2002-10-13
 3      |  348     |   2003-01-01

--

table B
rev_id | words
---------------
231    | 'ask'
231    | 'the'
231    | 'if'
345    | 'ask'
324    | 'ask'
324    | 'if'
348    | 'who'

此处神奇的 SQL 已编辑以显示其计算方式 {page_id : [words]}

date        |  count(words)
--------------------------
2002-01-01  |    3           { 1:[ask, the, if] }
2002-10-12  |    4           { 1:[ask, the, if], 2:[ask] }
2002-10-13  |    3           { 1:[ask, if], 2:[ask] }
2003-01-01  |    4           { 1:[ask, if], 2:[ask], 3:[who] }

我执行了此查询，但我的日期是固定的，我需要表修订中包含的所有日期:

SELECT SUM(q) 
FROM (
    SELECT COUNT(equation) q 
    FROM revision r, equation e
    WHERE r.rev_id in (
        SELECT max(rev_id) 
        FROM revision 
        WHERE date < '2006-01-01' 
        GROUP BY page_id
    ) 
    AND r.rev_id = e.rev_id
    GROUP BY date
) q;

<小时/>

已解决

我的 friend 帮助我创建查询来解决我的问题!

select s.date, count(words) from
(select d.date, r.page_id, max(r.rev_id) as rev_id 
    from revision r, (select distinct(date) from revision) d 
    where d.date >= r.date group by d.date, r.page_id) s
join words e on e.rev_id = s.rev_id
group by s.date;

Answer 1

我认为这是一个基本的join和group by:

select a.date, count(*)
from a join
     b
     on a.rev_id = b.rev_id
group by a.date;

编辑:

哦，我想我明白了。这是一个累积的事情。这使得事情变得更加复杂。

select d.date,
       (select count(*)
        from a join
             b
             on a.rev_id = b.rev_id
        where a.date <= d.date and
              a.rev_id = (select max(a2.rev_id) from a a2 where a2.date = a.date and a2.date <= d.date)
       ) as cnt
from (select date from a) d;

但是由于相关子句的嵌套，这在 MySQL 中不起作用。因此，我们可以将逻辑重构为:

select a.date, count(*)
from (select a.*,
             (select max(a2.rev_id)
              from a a2
              where a2.date <= a.date and a2.page_id = a.page_id
             ) as last_rev_id
      from a
     ) a join
     b
     on a.last_rev_id = b.rev_id
group by a.date;