一、当日每小时
当日每小时订单量
SELECT a.hour time, ifnull(b.count, 0) count
FROM (
SELECT 0 hour
UNION ALL SELECT 1 hour
UNION ALL SELECT 2 hour UNION ALL SELECT 3 hour
UNION ALL SELECT 4 hour UNION ALL SELECT 5 hour
UNION ALL SELECT 6 hour UNION ALL SELECT 7 hour
UNION ALL SELECT 8 hour UNION ALL SELECT 9 hour
UNION ALL SELECT 10 hour UNION ALL SELECT 11 hour
UNION ALL SELECT 12 hour UNION ALL SELECT 13 hour
UNION ALL SELECT 14 hour UNION ALL SELECT 15 hour
UNION ALL SELECT 16 hour UNION ALL SELECT 17 hour
UNION ALL SELECT 18 hour UNION ALL SELECT 19 hour
UNION ALL SELECT 20 hour UNION ALL SELECT 21 hour
UNION ALL SELECT 22 hour UNION ALL SELECT 23 hour ) a
LEFT JOIN (
SELECT hour(addtime) hour,count(addtime) count FROM order_m
WHERE date_format(addtime, '%Y-%m-%d') = current_date
GROUP BY date_format(addtime, '%Y%m%d-%H'), hour
) b
ON a.hour=b.hour
WHERE a.hour <=hour(now())
ORDER BY time
order_m是我自己的表
二、当月每日
当月每日
SELECT date
FROM (select DATE_ADD(t1.dayed,INTERVAL t2.number DAY) date
from (select DATE_ADD(curdate(),interval -day(curdate())+1 day) dayed) t1,
(SELECT @xi:=@xi+1 as number from
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8) xc2,
(SELECT @xi:=-1) xc0 ) t2
where DATE_ADD(t1.dayed ,INTERVAL t2.number DAY))time WHERE SUBSTRING(date,1,7)=SUBSTRING(CURDATE(),1,7)
当月每日订单量
SELECT p2.date time,COUNT(p1.id) count FROM
(
SELECT date
FROM (select DATE_ADD(t1.dayed,INTERVAL t2.number DAY) date
from (select DATE_ADD(curdate(),interval -day(curdate())+1 day) dayed) t1,
(SELECT @xi:=@xi+1 as number from
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8) xc2,
(SELECT @xi:=-1) xc0 ) t2
where DATE_ADD(t1.dayed ,INTERVAL t2.number DAY)) time WHERE SUBSTRING(date,1,7)=SUBSTRING(CURDATE(),1,7)
) p2
LEFT JOIN order_m p1 on to_days(p2.date)= to_days(p1.addtime)
WHERE to_days(p2.date) <=to_days(now())
group by to_days(p2.date)
ORDER BY to_days(p2.date)