使用mysql统计一周内数据

sql语句

按天统计

SELECT FROM_UNIXTIME(`create_time`,'%Y-%m-%d') time,count(id) counts FROM xcx_order where order_status = 2  GROUP BY  time

image.png

按天统计数据,没有则补0

SELECT day_list.day as date,IFNULL(data.count, 0) as counts from

(select FROM_UNIXTIME(create_time, '%Y-%m-%d') day, count(id) count from xcx_order WHERE order_status in(2,3,4) GROUP BY day) data

right join

(SELECT @date := DATE_ADD(@date, interval 1 day) day from

(SELECT @date := DATE_ADD('2020-7-1', interval -1 day) from xcx_order)

days LIMIT 7) day_list on day_list.day = data.day

image.png