mysql 按时间段并排比较列聚合

Question

我有两个表，2019q1 和 2019q2，我可以像平常一样单独聚合一列:

    mysql> select tname, sum(mhsaved) from teams a, 2019q1 b
    -> where a.tid = b.tid
    -> group by tname order by tname;
+----------------------------+--------------+
| tname                      | sum(mhsaved) |
+----------------------------+--------------+
| AST                        |        95.00 |
| Desktop support            |        23.00 |
| Middleware                 |        38.00 |
| MSSql database             |       351.00 |
| Oracle database            |       609.00 |
| SCM                        |       187.00 |
| Storage                    |        33.00 |
| Systems Engineering        |      2448.00 |
| UNIX Engineering           |        37.00 |
| UNIX Support               |       349.00 |
| Virtualization Engineering |        17.00 |
| Windows server support     |       913.00 |
+----------------------------+--------------+
12 rows in set (0.03 sec)

我想并排查看 2019q1 和 2019q2 的结果；但是，我无法完全理解解决方案。

有人能指出我正确的方向吗？

谢谢

道格·奥利里

Answer 1

与从每个表聚合的子查询连接。

SELECT tname, IFNULL(q1.mhsaved, 0.00) AS mhsaved_q1, IFNULL(q2.mhsaved, 0.00) AS mhsaved_q2
FROM teams AS t
LEFT JOIN (
    SELECT tid, SUM(mhsaved) AS mhsaved
    FROM 2019q1
    GROUP BY tid
) AS q1 ON t.tid = q1.tid
LEFT JOIN (
    SELECT tid, SUM(mhsaved) AS mhsaved
    FROM 2019q2
    GROUP BY tid
) AS q2 ON t.tid = q2.tid
ORDER BY tname