Mysql:某年12个月的总和，同月左连接

Question

我有一张收入表

+------------+--------+--------+
|    Date    | Amount | Status |
+------------+--------+--------+
| 2012-01-03 |    200 |      4 |
| 2012-02-12 |    300 |      4 |
| 2012-07-07 |    200 |      4 |
| 2012-08-15 |    300 |      4 |
| 2012-08-17 |    250 |      4 |
| 2012-09-17 |    250 |      3 |
+------------+--------+--------+

还有一个结果

+------------+--------+
|    Date    | Amount |
+------------+--------+
| 2012-03-22 |    50  |
| 2012-07-02 |    50  |
| 2012-08-19 |    100 |
| 2012-08-22 |    70  |
+------------+--------+

当我加入这两个表时(第一个只有状态为 4 的表)，我需要得到这个结果:

+------+-----------+--------+---------+--------+
| Year |   Month   | Income | Outcome | Profit |
+------+-----------+--------+---------+--------+
| 2012 | January   |    200 |       0 |    200 |
| 2012 | February  |    300 |       0 |    300 |
| 2012 | March     |      0 |      50 |    -50 |
| 2012 | April     |      0 |       0 |      0 |
| 2012 | Jun       |      0 |       0 |      0 |
| 2012 | July      |    200 |      50 |    150 |
| 2012 | August    |    550 |     170 |    380 |
| 2012 | September |      0 |       0 |      0 |
| 2012 | October   |      0 |       0 |      0 |
| 2012 | November  |      0 |       0 |      0 |
| 2012 | December  |      0 |       0 |      0 |
+------+-----------+--------+---------+--------+

如何查询得到这样的结果？

非常感谢，

Answer 1

首先，您需要将 2 个表合并为一个表，以便能够按月对它们进行分组。为此，我使用了 UNION。整个查询最终是这样的:

SELECT DATE_FORMAT(date, '%Y') AS year, DATE_FORMAT(date, '%M') AS month, SUM(income) AS income, SUM(outcome) AS outcome, (SUM(income)-SUM(outcome)) AS profit
FROM (
        SELECT date, amount AS income, 0 AS outcome FROM income_t
    UNION
        SELECT date, 0 AS income, amount AS outcome FROM outcome_t
    UNION
        SELECT '2012-01-01' AS date, 0 AS income, 0 AS outcome
    UNION
        SELECT '2012-02-01' AS date, 0 AS income, 0 AS outcome
    UNION
        SELECT '2012-03-01' AS date, 0 AS income, 0 AS outcome
    UNION
        SELECT '2012-04-01' AS date, 0 AS income, 0 AS outcome
    UNION
        SELECT '2012-05-01' AS date, 0 AS income, 0 AS outcome
    UNION
        SELECT '2012-06-01' AS date, 0 AS income, 0 AS outcome
    UNION
        SELECT '2012-07-01' AS date, 0 AS income, 0 AS outcome
    UNION
        SELECT '2012-08-01' AS date, 0 AS income, 0 AS outcome
    UNION
        SELECT '2012-09-01' AS date, 0 AS income, 0 AS outcome
    UNION
        SELECT '2012-10-01' AS date, 0 AS income, 0 AS outcome
    UNION
        SELECT '2012-11-01' AS date, 0 AS income, 0 AS outcome
    UNION
        SELECT '2012-12-01' AS date, 0 AS income, 0 AS outcome
) AS merged
GROUP BY DATE_FORMAT(date, '%Y%m')
ORDER BY date ASC;

我测试了它，它工作正常。

---

好吧，我确实误解了你关于所有月份的问题，但现在如你所愿。