这是我的mysql收入表。
+----+------------------+---------------------------+------------+---------+
| id | title | description | date | amount |
+----+------------------+---------------------------+------------+---------+
| 1 | Vehicle sales up | From new sale up | 2016-09-09 | 9999.99 |
| 2 | Jem 2 Sales | From rathnapura store | 2016-05-15 | 9545.25 |
| 3 | Jem 2 Sales 2 | From rathnapura store | 2016-05-15 | 9545.25 |
| 4 | Jem 2 Sales 2 | From rathnapura store 234 | 2016-05-15 | 9545.25 |
+----+------------------+---------------------------+------------+---------+
字段“日期”是标准 SQL 日期。我执行此查询是为了按月获取收入总和，如果某个月没有收入则返回零。如果某个月份没有收入，我想要零，因为我想在图表中显示这些数据。
这是查询。
SELECT MONTHNAME(`date`) AS mName, MONTH(`date`) AS mOrder, ifnull(sum(amount),0) AS total_num FROM income GROUP BY mOrder ORDER BY mOrder DESC
但我只得到如下输出。如果其他月份没有值，则没有零。这是输出。
+-----------+--------+-----------+
| mName | mOrder | total_num |
+-----------+--------+-----------+
| September | 9 | 9999.99 |
| May | 5 | 28635.75 |
+-----------+--------+-----------+
我希望上表中的其他月份和total_num为零。我怎样才能做到这一点？那里也有同样的问题。但没有有效的答案。按月分组，如果没有找到数据则返回0
请帮我解决这个问题。我用于此应用程序的语言是 Node.JS :)
列出所有月份的表格，然后left join到您的餐桌：
SELECT MONTHNAME(m.month) AS mName,
MONTH(m.month) AS mOrder,
ifnull(sum(amount),0) AS total_num
from months m
left join income i
on m.month = i.date
GROUP BY mOrder
ORDER BY mOrder DESC
如果您不想创建months表然后你可以：
(select STR_TO_DATE('01/01/2016', '%d/%m/%Y') as month union
select STR_TO_DATE('01/02/2016', '%d/%m/%Y') as month union
select STR_TO_DATE('01/03/2016', '%d/%m/%Y') as month union
select STR_TO_DATE('01/04/2016', '%d/%m/%Y') as month union
select STR_TO_DATE('01/05/2016', '%d/%m/%Y') as month union
select STR_TO_DATE('01/06/2016', '%d/%m/%Y') as month union
select STR_TO_DATE('01/07/2016', '%d/%m/%Y') as month union
select STR_TO_DATE('01/08/2016', '%d/%m/%Y') as month union
select STR_TO_DATE('01/09/2016', '%d/%m/%Y') as month union
select STR_TO_DATE('01/10/2016', '%d/%m/%Y') as month union
select STR_TO_DATE('01/11/2016', '%d/%m/%Y') as month union
select STR_TO_DATE('01/12/2016', '%d/%m/%Y') as month)
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
