mysql查询一周日期

基础概念

MySQL是一种关系型数据库管理系统，广泛用于存储和管理数据。在MySQL中，查询数据通常使用SQL（结构化查询语言）。查询一周的日期涉及到日期函数和时间操作。

类型

查询一周日期的类型主要包括以下几种：

获取当前日期所在周的所有日期
获取指定日期所在周的所有日期
获取某一周的所有日期

应用场景

日历应用：生成日历视图，显示一周的日期。
任务管理：显示某一周的任务安排。
数据分析：按周统计和分析数据。

示例代码

获取当前日期所在周的所有日期

SELECT 
    DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE()) - 2 DAY) AS 'Monday',
    DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE()) - 1 DAY) AS 'Tuesday',
    CURDATE() AS 'Wednesday',
    DATE_ADD(CURDATE(), INTERVAL 1 DAY) AS 'Thursday',
    DATE_ADD(CURDATE(), INTERVAL 2 DAY) AS 'Friday',
    DATE_ADD(CURDATE(), INTERVAL 3 DAY) AS 'Saturday',
    DATE_ADD(CURDATE(), INTERVAL 4 DAY) AS 'Sunday';

获取指定日期所在周的所有日期

SET @date = '2023-10-05';

SELECT 
    DATE_SUB(@date, INTERVAL DAYOFWEEK(@date) - 2 DAY) AS 'Monday',
    DATE_SUB(@date, INTERVAL DAYOFWEEK(@date) - 1 DAY) AS 'Tuesday',
    @date AS 'Wednesday',
    DATE_ADD(@date, INTERVAL 1 DAY) AS 'Thursday',
    DATE_ADD(@date, INTERVAL 2 DAY) AS 'Friday',
    DATE_ADD(@date, INTERVAL 3 DAY) AS 'Saturday',
    DATE_ADD(@date, INTERVAL 4 DAY) AS 'Sunday';

获取某一周的所有日期

SET @year = 2023;
SET @week = 40;

SELECT 
    DATE_ADD(DATE_SUB('1970-01-01', INTERVAL DAYOFWEEK('1970-01-01') - 2 DAY), INTERVAL (@year - 1970) * 52 + @week * 7 - 6 DAY) AS 'Monday',
    DATE_ADD(DATE_SUB('1970-01-01', INTERVAL DAYOFWEEK('1970-01-01') - 2 DAY), INTERVAL (@year - 1970) * 52 + @week * 7 - 5 DAY) AS 'Tuesday',
    DATE_ADD(DATE_SUB('1970-01-01', INTERVAL DAYOFWEEK('1970-01-01') - 2 DAY), INTERVAL (@year - 1970) * 52 + @week * 7 - 4 DAY) AS 'Wednesday',
    DATE_ADD(DATE_SUB('1970-01-01', INTERVAL DAYOFWEEK('1970-01-01') - 2 DAY), INTERVAL (@year - 1970) * 52 + @week * 7 - 3 DAY) AS 'Thursday',
    DATE_ADD(DATE_SUB('1970-01-01', INTERVAL DAYOFWEEK('1970-01-01') - 2 DAY), INTERVAL (@year - 1970) * 52 + @week * 7 - 2 DAY) AS 'Friday',
    DATE_ADD(DATE_SUB('1970-01-01', INTERVAL DAYOFWEEK('1970-01-01') - 2 DAY), INTERVAL (@year - 1970) * 52 + @week * 7 - 1 DAY) AS 'Saturday',
    DATE_ADD(DATE_SUB('1970-01-01', INTERVAL DAYOFWEEK('1970-01-01') - 2 DAY), INTERVAL (@year - 1970) * 52 + @week * 7 DAY) AS 'Sunday';