mysql统计近七天的数据并分组没有的为0

SELECT
    a.click_date,
    IFNULL(b.count,0)count
FROM
    (
SELECT
    curdate( ) AS click_date UNION ALL
SELECT
    date_sub( curdate( ), INTERVAL 1 DAY ) AS click_date UNION ALL
SELECT
    date_sub( curdate( ), INTERVAL 2 DAY ) AS click_date UNION ALL
SELECT
    date_sub( curdate( ), INTERVAL 3 DAY ) AS click_date UNION ALL
SELECT
    date_sub( curdate( ), INTERVAL 4 DAY ) AS click_date UNION ALL
SELECT
    date_sub( curdate( ), INTERVAL 5 DAY ) AS click_date UNION ALL
SELECT
    date_sub( curdate( ), INTERVAL 6 DAY ) AS click_date 
    ) a
    LEFT JOIN ( SELECT date( create_date ) AS create_date, count( * ) AS count FROM vip_experience GROUP BY date( create_date ) ) b ON a.click_date = b.create_date;

 

下面这个是我查询的sql

SELECT
    a.*,
    ifnull( b.inlet_flow, 0 ) inlet_flow,
    ifnull( b.passen_flow, 0 ) passen_flow,
    ifnull( b.entry_rate, 0 ) entry_rate
FROM
    (
SELECT
    curdate( ) AS date UNION ALL
SELECT
    date_sub( curdate( ), INTERVAL 1 DAY ) AS date UNION ALL
SELECT
    date_sub( curdate( ), INTERVAL 2 DAY ) AS date UNION ALL
SELECT
    date_sub( curdate( ), INTERVAL 3 DAY ) AS date UNION ALL
SELECT
    date_sub( curdate( ), INTERVAL 4 DAY ) AS date UNION ALL
SELECT
    date_sub( curdate( ), INTERVAL 5 DAY ) AS date UNION ALL
SELECT
    date_sub( curdate( ), INTERVAL 6 DAY ) AS date 
    ) a
    LEFT JOIN (
SELECT
    temp.date,
    temp.inlet_flow,
    temp.passen_flow,
    temp.entry_rate 
FROM
    (
SELECT
    date_format( mt.trace_date, '%Y-%m-%d' ) AS date,
    count( ve.create_date ) AS inlet_flow,
    count( mt.trace_date ) AS passen_flow,
    CONCAT( ROUND( count( ve.create_date ) / count( mt.trace_date ) * 100, 2 ), '', '%' ) AS entry_rate 
FROM
    member_footprint mt
    LEFT JOIN vip_experience ve ON mt.face_id = ve.face_id 
    #AND date_format( mt.trace_date, '%Y-%m-%d' ) = date_format( ve.create_date, '%Y-%m-%d' )
    LEFT JOIN device d ON mt.cid = d.dev_cid 
WHERE
    #DATE_SUB( CURDATE( ), INTERVAL 7 DAY ) < date( now( ) ) 
    #AND
    d.shop_id = 1 
GROUP BY
    date_format( mt.trace_date, '%Y-%m-%d' ) 
    ) temp 
    ) b ON a.date = b.date 
ORDER BY
    a.date ASC

上面的select是做个7天的日期查询，后面的SQL是业务的数据，然后根据date就是格式化后的日期分组，然后用date就是具体的日期join一下，留下符合 日期条件的数据，可以join也可以用in做子查询，看下下面的效果