做报表或者数据统计的时候可能会需要展示出每年,或者各种时间段的数据,这时,查询出的数据再有些月份为空,我们就得来填充这些数据。
select item.createTime as createTime,sum(item.total) as number FROM
(SELECT
DATE_FORMAT( spi.create_time, "%Y-%m" ) AS createTime,
count( spi.id ) total
FROM
`purchase_store_bill` spi
WHERE
spi.create_time BETWEEN DATE_SUB( now(), INTERVAL 1 YEAR)
AND now()
GROUP BY
DATE_FORMAT( spi.create_time, "%Y-%m" ) DESC
union all
SELECT
DATE_FORMAT( oiw.create_time, "%Y-%m" ) AS createTime,
count( oiw.id ) total
FROM
`other_in_warehouse` oiw
WHERE
oiw