使用SQL统计一个月每天数据_sql查询一个月的每天数据量-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_43830264/article/details/120132534

本文介绍了如何从mysql数据库中查询特定月份每天的数据，包括按帮助话题ID递增排序和关联业务数据的示例。还展示了查询过去30天和本年12个月数据的方法。主要涉及SQL查询技巧和日期格式处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、查某个月每天的数据

a.查询某个月每天的日期
SELECT 
    DATE_ADD(
      DATE_ADD(
        DATE_ADD(
          LAST_DAY(STR_TO_DATE('2021-09-06', "%Y-%m-%d")),
          INTERVAL 1 DAY
        ),
        INTERVAL - 1 MONTH
      ),
      INTERVAL (
        CAST(help_topic_id AS SIGNED INTEGER)
      ) DAY
    ) md 
  FROM
    mysql.help_topic 
  WHERE help_topic_id < DAY(
      LAST_DAY(STR_TO_DATE('2021-09-06', "%Y-%m-%d"))
    ) 
  ORDER BY help_topic_id

在这里插入图片描述

b.关联业务数据查询
SELECT 
  DATE_FORMAT(tmd.md, "%Y-%m-%d") `date`,b.id ,c.id
FROM
  (SELECT 
    DATE_ADD(
      DATE_ADD(
        DATE_ADD(
          LAST_DAY(STR_TO_DATE('2021-09-06', "%Y-%m-%d")),
          INTERVAL 1 DAY
        ),
        INTERVAL - 1 MONTH
      ),
      INTERVAL (
        CAST(help_topic_id AS SIGNED INTEGER)
      ) DAY
    ) md 
  FROM
    mysql.help_topic 
  WHERE help_topic_id < DAY(
      LAST_DAY(STR_TO_DATE('2021-09-06', "%Y-%m-%d"))
    ) 
  ORDER BY help_topic_id) tmd 
  LEFT JOIN b  
    ON DATE_FORMAT(tmd.md, "%Y-%m-%d") = DATE_FORMAT(b.startTime, "%Y-%m-%d") 
  LEFT JOIN c  
    ON c.id=b.id
WHERE (c.id = 1  OR c.id IS NULL) 
GROUP BY DATE_FORMAT(tmd.md, "%Y-%m-%d") 
  ORDER BY `date`

2、查询过去三十天的数据

  SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -help_topic_id DAY),'%m-%d')  AS ttt,help_topic_id
    FROM mysql.help_topic
    WHERE help_topic_id <  30

3、查询本年12个月的数据


SELECT
	DATE_FORMAT(
		DATE_SUB(
			CONCAT(YEAR(CURDATE()), '-12-31'),
			INTERVAL (
				CAST(help_topic_id AS SIGNED) - 0
			) MONTH
		),
		'%Y-%m'
	) MONTH
FROM
	mysql.help_topic
WHERE
	
	help_topic_id < 12
ORDER BY
	help_topic_id