Mysql统计数据，数据为空补0

之前项目中做了些根据特定时间做报表统计的功能，这里简单记录下，希望能帮到不会的同学。

1.根据指定年份，统计某年全年每月的数据，数据为空补0。（没有为系统维护时间表情况 create_time为时间戳  毫秒级别）

 sql如下：

SELECT
        a.`month` AS `month`,
        ifnull( b.backAmtStr, '0.00' ) as backAmtStr,
        ifnull( b.finishedAmtStr, '0.00' ) as finishedAmtStr,
        ifnull( b.invoiceAmtStr, '0.00' ) as invoiceAmtStr
        FROM
            (
        SELECT
            CONCAT(#{date},'-01') AS `month` UNION
        SELECT
            CONCAT(#{date},'-02') AS `month` UNION
        SELECT
            CONCAT(#{date},'-03') AS `month` UNION
        SELECT
            CONCAT(#{date},'-04') AS `month` UNION
        SELECT
            CONCAT(#{date},'-05') AS `month` UNION
        SELECT
            CONCAT(#{date},'-06') AS `month` UNION
        SELECT
            CONCAT(#{date},'-07') AS `month` UNION
        SELECT
            CONCAT(#{date},'-08') AS `month` UNION
        SELECT
            CONCAT(#{date},'-09') AS `month` UNION
        SELECT
            CONCAT(#{date},'-10') AS `month` UNION
        SELECT
            CONCAT(#{date},'-11') AS `month` UNION
        SELECT
            CONCAT( #{date},'-12') AS `month`
            ) a
            LEFT JOIN (
        SELECT
            from_unixtime( tc.create_time / 1000, '%Y-%m' ) AS `month`,
            CAST( IFNULL( sum( tcs.order_amt ), 0 ) / 100 AS DECIMAL ( 20, 2 ) ) AS backAmtStr,
            CAST( IFNULL( sum( tcs.order_in_amount ), 0 ) / 100 AS DECIMAL ( 20, 2 ) ) AS finishedAmtStr,
            CAST( IFNULL( sum( tcs.invoice_amount ), 0 ) / 100 AS DECIMAL ( 20, 2 ) ) AS invoiceAmtStr