When making report data statistics, a line chart is used to display and count the data settlement in the last six months. For the convenience of front-end partners in data processing, the month without data needs to be filled with 0 by default.

After a long online search, I finally found a solution and wrote it out to share with you. I hope it can help you.

1. Initialize last 6 months

SELECT
    CONVERT (t2.year_month_str, CHAR) time stamp
FROM
    (
        SELECT
            @rownum :=@rownum + 1 AS num,
            date_format(
                DATE_SUB(now(), INTERVAL @rownum MONTH),
                '%Y/%m'
            ) AS year_month_str
        FROM
            (SELECT @rownum := - 1) AS r_init,
            (
                SELECT
                    c.id
                FROM
                    yecaishui_tax_return_table_common_items c
                LIMIT 6
            ) AS c_init
    ) t2
GROUP BY
    t2.year_month_str

As a constant table in the library, as long as the number of data records is greater than 6, LIMIT 6 limits the number of queries. Here are the results

2. Associate the data table of the query with the LEFT JOIN keyword

SELECT
    IFNULL(SUM(i.total), 0.00) AS statementTotalMoney,
    IFNULL(SUM(i.taal_open), 0.00) AS statementSettledMoney,
    CONVERT (t2.year_month_str, CHAR) `timeStamp`
FROM
    (
        SELECT
            @rownum :=@rownum + 1 AS num,
            date_format(
                DATE_SUB(now(), INTERVAL @rownum MONTH),
                '%Y/%m'
            ) AS year_month_str
        FROM
            (SELECT @rownum := - 1) AS r_init,
            (
                SELECT
                    c.id
                FROM
                    yecaishui_tax_return_table_common_items c
                LIMIT 6
            ) AS c_init
    ) t2
LEFT JOIN yecaishui_income_statement AS i ON (
    CONCAT(
        DATE_FORMAT(i.gmt_create, '%Y'),
        '/',
        DATE_FORMAT(i.gmt_create, '%m')
    ) = t2.year_month_str
    AND i.delete_flag = 0
)
GROUP BY
    t2.year_month_str

... = t2.year'month'str indicates that the time field in the library is formatted and associated with the month. The query results are as follows

Reference blog:
https://www.jianshu.com/p/47f5b6846df0