mysql - 我如何在 MYSQL 中执行此操作？

Question

我在 MySQL 中有以下个人财务示例数据:

+---------------------------------------------------------------------------+
| id |    date    | amount | year | month |  description   | type | balance |
+---------------------------------------------------------------------------+
|  4 | 2015-02-03 | 563.00 | 2015 |   2   | Some text here |  out | -342.00 |
+---------------------------------------------------------------------------+
|  3 | 2015-02-01 | 102.95 | 2015 |   2   | Some text here |  in  |  221.00 |
+---------------------------------------------------------------------------+
|  2 | 2015-01-17 | 586.38 | 2015 |   1   | Some text here |  out |  184.96 |
+---------------------------------------------------------------------------+
|  1 | 2015-01-09 | 421.15 | 2015 |   1   | Some text here |  in  |  771.34 |
+---------------------------------------------------------------------------+

我想要的是某种 View /查询。所选年份中每个月的一行，其中 SUM(amount) 对于每种类型是分开的，有点像这样:

+-------------------------------------+
| year | month | total_in | total_out | 
+-------------------------------------+
| 2015 |   2   |  xxx.xx  |  xxx.xx   |
+-------------------------------------+
| 2015 |   1   |  xxx.xx  |  xxx.xx   |
+-------------------------------------+

使用以下查询

SELECT year, month, type, SUM(amount) as total_amount
FROM table
WHERE year = 2015
GROUP BY month, type
ORDER BY month
DESC

我只做到这一点:

+------------------------------------+
| year | month | type | total_amount |
+------------------------------------+
| 2015 |   2   |  in  |    xxx.xx    |
+------------------------------------+
| 2015 |   2   |  out |    xxx.xx    |
+------------------------------------+
| 2015 |   1   |  in  |    xxx.xx    |
+------------------------------------+
| 2015 |   1   |  out |    xxx.xx    |
+------------------------------------+

我在这里缺少什么？

谢谢!

Answer 1

你很接近。您需要条件聚合:

SELECT year, month, 
       SUM(case when type = 'in' then amount else 0 end) as total_in,
       SUM(case when type = 'out' then amount else 0 end) as total_out
FROM table
WHERE year = 2015
GROUP BY year, month
ORDER BY year desc, month DESC;