Mysql:某年12个月的总和，同月的left join
Mysql:某年12个月的总和，同月的left join
提问于 2013-05-27 04:28:14
我只有一张表收入
+------------+--------+--------+
| 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 |
+------------+--------+当我连接这两个表时(第一个表的status是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 |
+------+-----------+--------+---------+--------+如何查询得到像这样的结果？
非常感谢,
回答 2
Stack Overflow用户
发布于 2013-05-27 04:40:47
首先，您需要将两个表合并为一个表，以便能够按月对其进行分组。为此，我使用了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;我测试了它，它工作得很好。
好吧，我确实误解了你关于几个月的问题，但现在它是你想要的。
Stack Overflow用户
发布于 2013-05-27 04:35:55
首先，您需要从包含所有12个月的驱动程序表开始。您请求的是输出中不在输入中的行：
select driver.year, driver.monname, coalesce(i.amt, 0) as Income,
coalesce(o.amt, 0) as Outcome,
coalesce(i.amt, 0) - coalesce(o.amt, 0) as Profit
from (select 'January' as monname, 1 as mon, 2012 as yr union all
select 'February', 2, 2012 union all
. . .
select 'December', 12, 2012
) driver left outer join
(select year(date) as yr, month(date) as mon, sum(amount) as amt
from income
where status = 4
group by year(date), month(date)
) i
on driver.yr = i.yr and driver.mon = i.mon left outer join
(select year(date) as yr, month(date) as mon, sum(amount) as amt
from outcome
group by year(date), month(date)
) o
on driver.yr = o.yr and driver.mon = o.mon;对于初学者来说，你的问题在几个方面都相当棘手。首先，您需要driver表来获取所有行(并且需要填充表中缺少的行)。其次，您需要注意如何混合来自不同表的数据。选择不当的连接将导致笛卡尔乘积。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/16763412
复制相关文章
相似问题
腾讯云开发者
