mysql - 当我使用month()填充数据时，mysql是否有fill函数()？

Question

我使用month()函数来选择数据，sql，结果是:

从 t_loan 中选择月份(loan_date)周一，总和(loan_amount)total_loan
按周一分组；
--- 周一总贷款
--- 6 1000
--- 12 2000

Now I want the result is:
---mon   total_loan
---1     0
--- 2    0
---3   0
--- 4  0
--- 5   0
---  6   1000
---  7   0
--- 8  0
--- 9   0
---  10   0
--- 11  0
---  12   2000`

当我使用month()填充数据时，mysql是否有fill函数()？

Answer 1

解决这个问题的最简单方法就是一个简单的 12 行联合所有子查询

SELECT
      mon.n, coalesce(total_loan,0) total_loan
FROM (
      select 1  n union all
      select 2  n union all
      select 3  n union all
      select 4  n union all
      select 5  n union all
      select 6  n union all
      select 7  n union all
      select 8  n union all
      select 9  n union all
      select 10 n union all
      select 11 n union all
      select 12
      ) mon
LEFT JOIN (
      SELECT
            MONTH(loan_date) mon
          , SUM(loan_amount) total_loan
      FROM t_loan
      GROUP BY
            mon
      ) g ON g.mon = mon.n