mysql 按照每天/每月等统计数据（转）

 

data_t为任意业务表  天数控制为后面的limit数量

SELECT @cdate := DATE_ADD(@cdate, INTERVAL +1 DAY) cday
  FROM( SELECT @cdate := DATE_ADD('2018-01-10', INTERVAL -1 DAY) FROM data_t limit 15) t0
  WHERE date(@cdate) <= DATE_ADD('2018-01-20', INTERVAL -1 DAY)

 

左关联详细业务

SELECT 
    dayTb.cday '日期' 
    ,IFNULL(tNumTb.num,0) '人数'
FROM (
    SELECT @cdate := DATE_ADD(@cdate, INTERVAL +1 DAY) cday
        FROM( SELECT @cdate := DATE_ADD('2018-01-10', INTERVAL -1 DAY) FROM data_t limit 30) t0
        WHERE date(@cdate) < DATE_ADD(NOW(), INTERVAL -1 DAY)
) dayTb
LEFT JOIN(
    SELECT date(t.create_time) cday,count( DISTINCT t.fid ) num FROM data_t t
        WHERE t.create_time >='2018-01-10'
        GROUP BY cday
) tNumTb ON tNumTb.cday = dayTb.cday