Mysql 添加新列以供选择

Question

我有下表

+-----+------------------+-------------+
| id  | name             |month_1      |
+-----+------------------+-------------+
|   1 | anna             |     15      |   
|   2 | bin              |     20      |
+-----+------------------+-------------+

当我做出选择时，我想再添加一列。

例如

SELECT id,name, money as month_1 FROM test where month(day)='1';

我想添加一列，如下所示:

SELECT id,name, money as month_1,money as month_2 
FROM test 
where where month(day)='1',where month(day)='2'

+-----+------------------+-------------+------------+
| id  | name             | month_1     |month_2  |
+-----+------------------+-------------+------------+
|   1 |anna              |     15      |       10    |
|   2 | bin              |     20      |       0     |
+-----+------------------+-------------+------------+

Answer 1

您可以使用条件聚合:

SELECT id,name,
       SUM(CASE WHEN month(day) = 1 THEN money ELSE 0 END) as month_1,
       SUM(CASE WHEN month(day) = 2 THEN money ELSE 0 END) as month_2
FROM test 
GROUP BY id, name;

您可能想也可能不想在聚合中包含月份和年份。