MySQL之统计查询，按年月日查询数据，无数据自动填充0

直接上代码

点击查看代码

SELECT
        date_list._date AS date,
        IFNULL( real_data.customerCnt, 0 ) AS customerCnt,
        IFNULL( real_data.addCnt, 0 ) AS addCnt,
        IFNULL( real_data.loseCnt, 0 ) AS loseCnt,
        IFNULL( real_data.applyCnt, 0 ) AS applyCnt
        FROM
        (
        SELECT
        ( @s := @s + 1 ) AS _index,
        STR_TO_DATE( DATE( DATE_SUB( DATE_FORMAT( #{dto.endTime}, '%Y-%m-%d' ), INTERVAL @s DAY ) ), '%Y-%m-%d' ) AS
        _date
        FROM
        information_schema.CHARACTER_SETS,
        ( SELECT @s :=- 1 ) AS init
        WHERE
        @s < datediff( DATE_FORMAT( #{dto.endTime}, '%Y-%m-%d' ), DATE_FORMAT( #{dto.beginTime}, '%Y-%m-%d' ) )
        ORDER BY
        _date
        ) AS date_list
        LEFT JOIN (
        SELECT
        DATE_FORMAT( a.report_date, '%Y-%m-%d' ) DAY
        FROM
        we a
        GROUP BY
        DAY
        ) AS real_data ON date_list._date = real_data.DAY
        ORDER BY date ASC

日期中间表生成

SELECT ( @s := @s + 1 ) AS _index, STR_TO_DATE( DATE( DATE_SUB( DATE_FORMAT( '2022-10-26', '%Y-%m-%d' ), INTERVAL @s DAY ) ), '%Y-%m-%d' ) AS _date FROM information_schema.CHARACTER_SETS, ( SELECT @s :=- 1 ) AS init WHERE @s < datediff( DATE_FORMAT( '2022-10-26', '%Y-%m-%d' ), DATE_FORMAT( '2022-10-20', '%Y-%m-%d' ) ) ORDER BY _date

最终结果