统计年月日sql

最新推荐文章于 2025-02-13 21:45:00 发布

原创最新推荐文章于 2025-02-13 21:45:00 发布 · 374 阅读

本内容遵循CC 4.0 BY-SA版权协议

统计一年的数据

SELECT
	MONTH (init_up_time) AS label,
	count(1) AS VALUE
FROM
	t_ac_device
GROUP BY
	MONTH (init_up_time)
ORDER BY
	label

统计当月以3天为一个点的数据

SELECT
  ceil(day(init_up_time)/3) AS label,
  count(1) AS value
  FROM
      t_ac_device
  WHERE
      MONTH (init_up_time) = MONTH ((SELECT CURDATE()))
  GROUP BY
  label

统计最近七天

SELECT
     a.click_date,
     ifnull(b.count, 0) AS count
 FROM
     (
         SELECT
             curdate() AS click_date
         UNION ALL
             SELECT
                 date_sub(curdate(), INTERVAL 1 DAY) AS click_date
             UNION ALL
                 SELECT
                     date_sub(curdate(), INTERVAL 2 DAY) AS click_date
                 UNION ALL
                     SELECT
                         date_sub(curdate(), INTERVAL 3 DAY) AS click_date
                     UNION ALL
                         SELECT
                             date_sub(curdate(), INTERVAL 4 DAY) AS click_date
                         UNION ALL
                             SELECT
                                 date_sub(curdate(), INTERVAL 5 DAY) AS click_date
                             UNION ALL
                                 SELECT
                                     date_sub(curdate(), INTERVAL 6 DAY) AS click_date
     ) a
 LEFT JOIN (
     SELECT
         date(init_up_time) AS datetime,
         count(*) AS count
     FROM
         t_ac_device
     GROUP BY
         date(init_up_time)
 ) b ON a.click_date = b.datetime;