mysql - 获取MySQL中每月的费用总额

Question

我目前正在处理 2 个表，即支出和收入。为了保持结构简单并且可以看到它，这是 fiddle :http://sqlfiddle.com/#!9/256cd64/2

我的查询需要的结果是当年每个月的总金额，为此并尝试了如下操作:

select sum(e.amount) as expense, DATE_FORMAT(e.date,'%m') as month 
from expenses e
where year(e.date) = 2019
group by month

我的问题是，这只需要我注册的几个月，而我希望无论他们是否注册，都需要 12 个月，以防他们没有返回 0 总额。

目前我正在处理费用表，但我想要一个返回每月费用和收入的查询，这是我希望获得的最终输出的示例:

|   Month | Expense| Incomes |
|---------|--------|---------|
|       01|      0 |       0 |
|       02|   3000 |    4000 |
|       03|   1500 |    5430 |
|       04|  2430  |    2000 |
|       05|  2430  |    1000 |
|       06|  2430  |    1340 |
|       07|     0  |    5500 |
|       08|  2430  |    2000 |
|       09|  1230  |    2000 |
|       10|  8730  |    2000 |
|       11|  2430  |    2000 |
|       12|  6540  |    2000 |

Answer 1

您需要生成月份值，然后使用左连接来匹配费用:

select coalesce(sum(e.amount), 0) as expense, m.month
from (select '01' as month union all
      select '02' as month union all
      select '03' as month union all
      select '04' as month union all
      select '05' as month union all
      select '06' as month union all
      select '07' as month union all
      select '08' as month union all
      select '09' as month union all
      select '10' as month union all
      select '11' as month union all
      select '12' as month
     ) m left join
     expenses e
     on year(e.date) = 2019 and
        DATE_FORMAT(e.date,'%m') = m.month
group by m.month;

Here是一个数据库<> fiddle 。

至于收入，您应该问另一个问题。