mysql - 在MySQL中，如何在连接公共(public)列时查询多个表对列的计数

Question

我有两张表(来自不同的原始数据源)

项目(横截面数据)

Timestamp   Project   Owner     Name     Submission_Date
2014-02-18  1         Tim       Susan    2014-02-10
2014-02-18  2         Matt      Jaclyn   2014-02-10
2014-02-18  2         Tim       Mary     2014-02-11
etc

和 Hitups(事件日志)

Project  Owner  Name   Hitup_Date
1        Tim    Susan  2014-02-01
2        Matt   Jaclyn 2014-02-02
etc

我想运行查询以从两个表中获取事件计数，并按公共(public)项目和所有者分组。鉴于以上完全构成的示例数据，我希望看到类似于

的结果

Project    Owner    count(Hitup_Date)   count(Submission_Date)
1          Tim      1                   1
2          Matt     1                   1
2          Tim      null                1

我的查询尝试如下

SELECT p.Project, p.Owner, COUNT(Hitup_Date), COUNT( p.Submission_Date ) 
FROM projects p, hitups h
WHERE p.project = h.project and p.owner = h.owner
AND p.date = ( SELECT MAX( DATE ) FROM projects ) 
GROUP BY p.project, p.owner

... 失败得很惨。出了什么问题？我进行了详尽的搜索，但未能找到关于如何明确解决类似情况的先前示例 - 感谢您的指导。

Answer 1

需要先进行分组聚合，然后再join结果:

SELECT p.project, p.owner, submission_count, hitup_count
FROM (SELECT   project, owner, COUNT(*) AS submission_count
      FROM     projects
      GROUP BY project, owner) p
LEFT OUT JOIN (SELECT project, owner, COUNT(*) AS hitup_count
               FROM hitups
               GROUP BY project, owner) h 
ON p.project = h.project AND p.owner = h.owner