mysql 当日每小时，当月每日

---

一、当日每小时

当日每小时订单量

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)