做报表统计的时候,常常需要在MySQL端用sql统计近7个月的数据,或者近7天的数据。
假如遇到当月没有数据,或者当天没有数据时,默认为0。
数据展示效果如下:
数据获取结果如下:
那么该如何获取这样的数据呢?
第一步,构造近7个月的日期数据
SELECT @rownum :=@rownum + 1 AS num,date_format(DATE_SUB(now(),INTERVAL @rownum MONTH),'%Y-%m') AS all_day FROM (SELECT @rownum := -1) AS r_init,(SELECT c.id FROM city c LIMIT 7) as c_init
数据获取结果如下:
解释一下:
- @rownum是一个变量,随着c_init的表记录条数逐一递增。
-
(SELECT c.id FROM city c LIMIT 7) as c_init
语句的limit来限制条数,需要7个月就为7。注意city是一个常量表,只要其条数大于7即可。limit 7 非常关键。 -
date_format(DATE_SUB(now(),INTERVAL @rownum MONTH),'%Y-%m') AS all_day
语句,从现在开始,往后推7个月。
第二步,LEFT JOIN 你的数据表
SELECT convert(t2.all_day, char) all_day,COALESCE(SUM(o.good_amount), 0) AS good_amount,COALESCE(SUM(o.order_count), 0) AS good_count,t2.num FROM (SELECT @rownum :=@rownum + 1 AS num,date_format(DATE_SUB(now(),INTERVAL @rownum MONTH),'%Y-%m') AS all_day FROM (SELECT @rownum := -1) AS r_init,(SELECT c.id FROM city c LIMIT 7) as c_init) t2 LEFT JOIN ym_good_orders o ON (CONVERT(FROM_UNIXTIME(o.order_time / 1000, '%Y-%m'), CHAR)=t2.all_day AND o.good_uid=25 and o.is_delete=0) GROUP BY t2.all_day;
有了7条日期记录,那么就可以使用left join把你想要的数据加载过来。
比如我这条sql语句中需要成交额good_amount,需要交易笔数good_count。
关注“沉默王二”公众号,与你分享IT干货、生活感悟、读书心得,还有声音驿站栏目等你哟!
微信扫一扫下方二维码即可关注: