MySQL中使用CTE获取时间段数据的技巧分享-编程学习网

引言

在数据库操作中，获取特定时间段的数据是一项常见任务。mysql自从8.0版本开始支持CTE（公共表表达式），使得我们可以更加灵活和高效地处理时间段数据。本文将介绍如何使用CTE获取最近十二个月、最近十二周、最近四个季度，以及如何获取十二个月前的月第一天和十二周前的周第一天。

1. 获取最近十二个月

当你需要获取最近十二个月的月份数据时，CTE可以帮助你生成一个包含这些月份的列表。

WITH RECURSIVE month_12 AS (
    SELECT CURDATE() AS date_day
    UNION ALL
    SELECT DATE_SUB(date_day, INTERVAL 1 MONTH)
    FROM month_12
    WHERE date_day > DATE_SUB(CURDATE(), INTERVAL 11 MONTH)
),
mon_12 AS (
 SELECT DATE_FORMAT(date_day, '%Y-%m') AS label FROM  month_12 ORDER BY date_day desc
)
 
select * from mon_12

解释：

通过 DATE_FORMAT(CURDATE(), '%Y-%m') 获取当前的年份和月份。
使用递归的方式生成从当前月往前推的十二个月。
最后，按年份和月份倒序排列，以确保最近的月份排在前面。

2. 获取最近十二周

如果你需要获取最近十二周的周数据，CTE也可以帮助你生成一个列表，其中每个条目表示一个特定的周。

WITH RECURSIVE week_date_12 AS (
    SELECT CURDATE() AS date_day
    UNION ALL
    SELECT DATE_SUB(date_day, INTERVAL 1 week)
    FROM week_date_12
    WHERE date_day > DATE_SUB(CURDATE(), INTERVAL 11 week)
),
week_12 AS (
 SELECT DATE_FORMAT(date_day, '%Y-%u') AS label FROM  week_date_12 ORDER BY date_day desc
)
SELECT label
FROM week_12

解释：

通过 YEARWEEK(CURDATE(), 3) 获取当前日期所在的年份和周数。
递归地从当前周开始，往前推十二周。
结果按年份和周数倒序排列，显示最近的十二周。

3. 获取最近四个季度

对于季度数据的处理，MySQL中的CTE可以帮助你生成最近四个季度的列表。

WITH RECURSIVE last_four_quarters AS (
    SELECT 
        YEAR(CURDATE()) AS year,
        QUARTER(CURDATE()) AS quarter
    UNION ALL
    SELECT 
        CASE 
            WHEN quarter = 1 THEN year - 1 
            ELSE year 
        END,
        CASE 
            WHEN quarter = 1 THEN 4 
            ELSE quarter - 1 
        END
    FROM last_four_quarters
    WHERE year > YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) OR quarter > QUARTER(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))
)
SELECT CONCAT(year, '-Q', quarter) AS year_quarter
FROM last_four_quarters
ORDER BY year DESC, quarter DESC;

解释：

使用 YEAR(CURDATE()) 和 QUARTER(CURDATE()) 获取当前的年份和季度。
递归生成最近四个季度的数据，确保季度的转换是正确的（例如，从第一季度到上一年的第四季度）。
最终结果按年份和季度倒序排列。

4. 获取十二个月前的月第一天

当你需要获取十二个月前的月份的第一天，可以使用以下查询：

SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 12 MONTH), '%Y-%m-01') AS first_day_of_month;

解释：

使用 DATE_SUB(CURDATE(), INTERVAL 12 MONTH) 来获取十二个月前的日期。
通过 DATE_FORMAT(..., '%Y-%m-01') 将这个日期格式化为该月份的第一天。

select LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 11 MONTH) - INTERVAL 1 MONTH) + INTERVAL 1 DAY

5. 获取十二周前的周第一天

要获取十二周前的周第一天（通常是周一），可以使用以下查询：

SELECT DATE_SUB(DATE_SUB(CURDATE(), INTERVAL 12 WEEK), INTERVAL WEEKDAY(DATE_SUB(CURDATE(), INTERVAL 12 WEEK)) DAY) AS first_day_of_week;

解释：

DATE_SUB(CURDATE(), INTERVAL 12 WEEK) 计算十二周前的日期。
使用 WEEKDAY() 函数计算该日期对应的周几（0表示周一，6表示周日）。
最后通过减去这个天数，得到十二周前的周一。

结论

MySQL中的CTE为我们提供了强大的工具来处理复杂的时间段计算。通过使用CTE，你可以轻松地生成最近十二个月、十二周、四个季度的数据，还可以精确地获取特定时间段的第一天。掌握这些技巧，可以让你的时间管理和数据处理更加高效、精确，为你的数据库操作带来更大的灵活性和可控性。

以上就是MySQL中使用CTE获取时间段数据的技巧分享的详细内容，更多关于MySQL CTE获取时间段数据的资料请关注编程网(www.lsjlt.com)其它相关文章！

阅读原文内容投诉

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

历年真题答案解析
备考技巧名师总结
高频考点精准押题

资料下载
历年真题

2024上半年软考中级软件测评师考试基础知识真题
193.9 KB下载数265
2024上半年软考中级软件设计师考试基础知识真题
191.63 KB下载数245
2023下半年-系统集成项目管理工程师-真题考点汇总（完整版）
143.91 KB下载数1148
2023年下半年系统集成项目管理工程师第一、二、三批次真题考点整理(考友回忆版)
183.71 KB下载数642
2023年上半年软考中级《系统集成项目管理工程师》-基础知识-考试真题及答案
644.84 KB下载数2756

2024年上半年信息系统项目管理师第二批次真题及答案解析（完整版）
难度 813人已做
查看
【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析
难度 354人已做
查看
【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析
难度 318人已做
查看
2024年上半年软考高项第一、二批次真题考点汇总（完整版）
难度 435人已做
查看
2024年上半年系统架构设计师考试综合知识真题
难度 224人已做
查看

发现更多好内容

猜你喜欢

AI推送时光机