我想回显每月创建的发票总数。
到目前为止,下面的代码可以正常工作,但如果一个月内没有结果,我需要 0。
$sql = "SELECT MONTH( invoice_date ) AS MONTH, SUM(`invoice`.`total`) as total
FROM `invoices`
WHERE YEAR(invoice_date) = '2019'
GROUP BY MONTH(invoice_date)";
$result = mysqli_query($con,$sql);
while($data = mysqli_fetch_array($result))
{
$total = $data['total'];
$month = $data['month'];
}
我想要的是这个:
Jan 500
Feb 300
Mar 0
Apr 500
etc...
我得到的是这个
1月 500 2月 300 4月 500
。因为我的数据库中没有带有 Mar 的条目。
如果我每个月都在循环中执行查询,它工作正常,但我知道我不应该以这种方式使用它,并且有更好的方法!?
使用UNION ALL
创建一个查询,该查询返回所有月份数字 1-12 和 LEFT JOIN 到您的查询中:
SELECT m.MONTH, COALESCE(t.total, 0)
FROM (
SELECT 1 AS MONTH UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
) AS m LEFT JOIN (
SELECT MONTH( invoice_date ) AS MONTH, SUM(`invoice`.`total`) as total
FROM `invoices`
WHERE YEAR(invoice_date) = '2019'
GROUP BY MONTH(invoice_date)
) AS t ON t.MONTH = m.MONTH