mysql按年月时间段查询没有值为0
设定时间初始表左外连接
SELECT
a.count_time yearMonth,
IFNULL(truncate(y.sellingAmount,2),0) totalSellingAmount,
IFNULL(truncate(b.totalBill,2),0) totalBill,
IFNULL(FORMAT(b.totalBill/y.sellingAmount*100,2),0) totalBillRate,
IFNULL(truncate(b.avgBill,2),0) avgBill
FROM
(SELECT DATE_FORMAT( @cdate := DATE_ADD( @cdate, INTERVAL + 1 MONTH ), ‘%Y-%m’ ) AS count_time FROM
( SELECT @cdate := DATE_ADD( ‘2018-03-01 00:00:00’, INTERVAL - 1 DAY ) FROM rdf_pl_bi_ebay_profit_summary) t0
WHERE
date( @cdate ) <= DATE_ADD( ‘2020-05-01 00:00:00’, INTERVAL - 1 DAY )) a
LEFT JOIN (
select
sum(selling_amount) sellingAmount,DATE_FORMAT(count_time, ‘%Y-%m’) count_time
from rdf_pl_bi_ebay_profit_summary
where
tb_status=0<