使用SQL统计一个月每天数据

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 FROMmysql.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 FROMmysql.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_idFROM mysql.help_topicWHERE help_topic_id <  30

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

SELECTDATE_FORMAT(DATE_SUB(CONCAT(YEAR(CURDATE()), '-12-31'),INTERVAL (CAST(help_topic_id AS SIGNED) - 0) MONTH),'%Y-%m') MONTH
FROMmysql.help_topic
WHEREhelp_topic_id < 12
ORDER BYhelp_topic_id

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！