传入一个月份获取该月的统计信息

   需要统计一个打卡习惯，要求是传入一个时间格式如“2019-07” ，获取整个月份的统计，没有的补0

下面直接上sql:

 

SELECT
    xx_date,
    MAX( SUM ) AS SUM 
FROM
    (
    SELECT
        d.xx_date AS xx_date,
        d.sum AS SUM 
    FROM
        (
        SELECT
            @num := @num + 1 num,
            0 AS `sum`,
            DATE_ADD( DATE_FORMAT( '2019-07-01', '%Y-%m-%d' ), INTERVAL @num DAY ) AS xx_date 
        FROM
            shopping_hibitRecord a,
            ( SELECT @num :=- 1 ) num_t 
        WHERE
            @num < ( SELECT DAYOFMONTH( LAST_DAY( '2019-07-01' ) ) - 1 ) 
        ) d UNION ALL
    SELECT
        DATE_FORMAT( ADDTIME, '%Y-%m-%d' ) AS `xx_date`,
        COUNT( * ) AS 'sum' 
    FROM
        `shopping_hibitRecord` 
    WHERE
        DATE_FORMAT( shopping_hibitRecord.`ADDTIME`, '%Y-%m' ) = '2019-07' 
        AND deleteStatus = FALSE 
        AND TYPE = 0 
        AND sendUser_id = 35920 
        AND habit_id = 298 
    GROUP BY
        xx_date DESC 
    ) b 
GROUP BY
    `xx_date` DESC

  这段代码分为两步:第一步，获取传入时间的月份的所有天数，并统计设为0

     如下：

    

SELECT
        d.xx_date AS xx_date,
        d.sum AS SUM 
    FROM
        (
        SELECT
            @num := @num + 1 num,
            0 AS `sum`,
            DATE_ADD( DATE_FORMAT( '2019-07-01', '%Y-%m-%d' ), INTERVAL @num DAY ) AS xx_date 
        FROM
            shopping_hibitRecord a,
            ( SELECT @num :=- 1 ) num_t 
        WHERE
            @num < ( SELECT DAYOFMONTH( LAST_DAY( '2019-07-01' ) ) - 1 ) 
        ) d 

第二步，从我们的记录表中筛选出打卡记录统计信息：

     

SELECT
        DATE_FORMAT( ADDTIME, '%Y-%m-%d' ) AS `xx_date`,
        COUNT( * ) AS 'sum' 
    FROM
        `shopping_hibitRecord` 
    WHERE
        DATE_FORMAT( shopping_hibitRecord.`ADDTIME`, '%Y-%m' ) = '2019-07' 
        AND deleteStatus = FALSE 
        AND TYPE = 0 
        AND sendUser_id = 35920 
        AND habit_id = 298 
    GROUP BY
        xx_date DESC 

结果是：

第三步，全连接后进行group by 时间

SELECT
    xx_date,
    MAX( SUM ) AS SUM 
FROM
    (
    SELECT
        d.xx_date AS xx_date,
        d.sum AS SUM 
    FROM
        (
        SELECT
            @num := @num + 1 num,
            0 AS `sum`,
            DATE_ADD( DATE_FORMAT( '2019-07-01', '%Y-%m-%d' ), INTERVAL @num DAY ) AS xx_date 
        FROM
            shopping_hibitRecord a,
            ( SELECT @num :=- 1 ) num_t 
        WHERE
            @num < ( SELECT DAYOFMONTH( LAST_DAY( '2019-07-01' ) ) - 1 ) 
        ) d UNION ALL
    SELECT
        DATE_FORMAT( ADDTIME, '%Y-%m-%d' ) AS `xx_date`,
        COUNT( * ) AS 'sum' 
    FROM
        `shopping_hibitRecord` 
    WHERE
        DATE_FORMAT( shopping_hibitRecord.`ADDTIME`, '%Y-%m' ) = '2019-07' 
        AND deleteStatus = FALSE 
        AND TYPE = 0 
        AND sendUser_id = 35920 
        AND habit_id = 298 
    GROUP BY
        xx_date DESC 
    ) b 
GROUP BY
    `xx_date` DESC

结果就是我要的数据：

  这仅仅是一种解决方法。