MYSQL 获取本周、本月每天，本季度、近半年、本年、近一年的每个月份

浴火龙王

已于 2022-04-21 09:35:26 修改

阅读量2.2k

点赞数 6

文章标签： mysql

于 2022-04-18 18:01:07 首次发布

本文链接：https://blog.csdn.net/qq_33118773/article/details/124255381

版权

1.日期范围获取每天（截止结束日期）

SELECT
	DATE_ADD( '2021-04-04', INTERVAL CAST( help_topic_id AS signed INTEGER ) DAY ) AS `date` 
FROM
	mysql.help_topic 
WHERE
	help_topic_id < DATEDIFF( '2021-07-04', '2021-04-04' ) + 1;

2. 日期范围获取每月（截止结束日期所在月）

SELECT
	DATE_FORMAT( DATE_ADD( '2021-04-04', INTERVAL CAST( help_topic_id AS signed INTEGER ) MONTH ), '%Y-%m' ) AS `month` 
FROM
	mysql.help_topic 
WHERE
	help_topic_id < TIMESTAMPDIFF( MONTH, '2021-04-04', '2021-07-04' ) + 1;

3.本周每天（截止今日）

SELECT
	DATE_FORMAT(DATE_ADD( DATE_SUB( NOW(), INTERVAL WEEKDAY( NOW()) + 0 DAY ), INTERVAL CAST( help_topic_id AS signed INTEGER ) DAY ), '%Y-%m-%d' ) AS `date` 
FROM
	mysql.help_topic 
WHERE
	help_topic_id <= WEEKDAY( NOW());

4.近7日每天（截止今日）

SELECT
	DATE_FORMAT( DATE_ADD( DATE_ADD( NOW(), INTERVAL - 6 DAY ), INTERVAL CAST( help_topic_id AS SIGNED INTEGER ) DAY ), '%Y-%m-%d' ) AS `date` 
FROM
	mysql.help_topic 
WHERE
	help_topic_id < 7;

5.本月每天（截止今日）

SELECT
	DATE_ADD( DATE_ADD( NOW(), INTERVAL - DAY ( NOW())+ 1 DAY ), INTERVAL CAST( help_topic_id AS signed INTEGER ) DAY ) AS `date` 
FROM
	mysql.help_topic 
WHERE
	help_topic_id < DAY ( NOW());

6.近30日每天（截止今日）

SELECT
	DATE_FORMAT( DATE_ADD( DATE_ADD( NOW(), INTERVAL - 29 DAY ), INTERVAL CAST( help_topic_id AS SIGNED INTEGER ) DAY ), '%Y-%m-%d' ) AS `date` 
FROM
	mysql.help_topic 
WHERE
	help_topic_id < 30;

7.本季度每月（截止所在月）

SELECT
	DATE_FORMAT( DATE_ADD( CONCAT( YEAR ( NOW()), '-0',(( QUARTER ( NOW()) - 1 )* 3 + 1 ), '-01' ), INTERVAL CAST( help_topic_id AS SIGNED INTEGER )MONTH  ), '%Y-%m' ) as `month`
FROM
	mysql.help_topic 
WHERE
	help_topic_id < MONTH ( NOW()) % 3;

8.近半年每月（截止所在月）

SELECT
	DATE_FORMAT( DATE_ADD( DATE_ADD( NOW(), INTERVAL - 5 MONTH ), INTERVAL CAST( help_topic_id AS SIGNED INTEGER ) MONTH ), '%Y-%m' ) as `month`
FROM
	mysql.help_topic 
WHERE
	help_topic_id < 6;

9.本年每月（截止所在月）

SELECT
	DATE_FORMAT( DATE_ADD( CONCAT( YEAR ( NOW()), '-01-01' ), INTERVAL CAST( help_topic_id AS SIGNED INTEGER ) MONTH ), '%Y-%m' ) as `month`
FROM
	mysql.help_topic 
WHERE
	help_topic_id < MONTH ( NOW());

10.近1年每月（截止所在月）

SELECT
	DATE_FORMAT( DATE_ADD( DATE_ADD( NOW(), INTERVAL - 11 MONTH ), INTERVAL CAST( help_topic_id AS SIGNED INTEGER ) MONTH ), '%Y-%m' ) as `month` 
FROM
	mysql.help_topic 
WHERE
	help_topic_id < 12;