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

fzwanglz

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;

rainerosion

挺实用的！！