• MySQL获取当天24小时的各个时间段
    1. SELECT
    2. a.hours,
    3. ifnull( count, 0 ) AS value
    4. FROM (
    5.   SELECT DATE_FORMAT( DATE_SUB( DATE_FORMAT( NOW(),'%Y-%m-%d'),INTERVAL ( -(@i:=@i+1) ) HOUR ) ,'%Y%m%d%H') AS hours
    6.   FROM (
    7.       SELECT a FROM 
    8.            (SELECT '1' AS a UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' ) AS a
    9.       JOIN ( SELECT  '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' ) AS b
    10.           ON 1
    11.       ) AS b , (SELECT @i:=-1)  AS i 
    12.    ) a
    13. LEFT JOIN (
    14.    SELECT DATE_FORMAT( trans_time, '%Y%m%d%H' ) hours , count( 0 )  AS count
    15.    FROM
    16.        tms_order
    17.    WHERE del_flag = 0 and order_status = 5
    18.    GROUP BY hours
    19. ) as b ON  a.hours  =  b.hours 
    20. ORDER BY hours asc

    参考：https://blog.csdn.net/zuochunping/article/details/102499637

  • 统计最近7天的数据

image.png

  1. SELECT
  2.     date(a.trans_time) as date,
  3.     ifnull( count, 0 ) AS value
  4. FROM
  5.     (
  6.     SELECT
  7.     date_sub( now(), INTERVAL 1 DAY ) AS trans_time UNION ALL
  8.     SELECT
  9.     date_sub( now(), INTERVAL 2 DAY ) AS trans_time UNION ALL
  10.     SELECT
  11.     date_sub( now(), INTERVAL 3 DAY ) AS trans_time UNION ALL
  12.     SELECT
  13.     date_sub( now(), INTERVAL 4 DAY ) AS trans_time UNION ALL
  14.     SELECT
  15.     date_sub( now(), INTERVAL 5 DAY ) AS trans_time UNION ALL
  16.     SELECT
  17.     date_sub( now(), INTERVAL 6 DAY ) AS trans_time UNION ALL
  18.     SELECT
  19.     date_sub( now(), INTERVAL 7 DAY ) AS trans_time
  20.     ) as a
  21. LEFT JOIN ( SELECT date(trans_time) AS trans_time, count( 0 )  AS count
  22. FROM
  23. tms_order 
  24. WHERE del_flag = 0 and order_status = 5
  25. GROUP BY
  26. date(trans_time)
  27. ) as  b ON date(a.trans_time) = date(b.trans_time)
  28. ORDER BY date asc

查询某月的