MySQL中使用CTE獲取時間段數據的技巧分享
更新時間：2024年08月21日 10:46:12 作者：nbsaas-boot
在數據庫操作中,獲取特定時間段的數據是一項常見任務,MySQL自從8.0版本開始支持CTE（公共表表達式）,使得我們可以更加靈活和高效地處理時間段數據,本文小編介紹了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獲取時間段數據的資料請關注腳本之家其它相關文章！
您可能感興趣的文章:
- MySQL CTE (Common Table Expressions)示例全解析
- MySQL CTE 通用表達式詳解
- 如何使用 Spring Boot 3.3 和 JdbcTemplate 操作 MySQL 數據庫
- MySQL數據庫中遇到no?database?selected問題解決辦法
- Mysql8公用表表達式CTE詳解
- MySQL8.0之CTE(公用表表達式)的使用
- 解決mysql報錯:Data?source?rejected?establishment?of?connection,?message?from?server:?\"Too?many?connectio
- MySQL數據庫之字符集?character
- mysql8 公用表表達式CTE的使用方法實例分析
- MySQL中普通CTE和遞歸CTE的順序問題小結
相關文章
這里記錄的是很久之前的一個 bug 了，主要給大家介紹了關于MySQL中唯一性約束與NULL的相關資料，文中介紹的非常詳細，對大家具有一定的參考學習價值，需要的朋友們下面來一起看看吧。2017-05-05
percona-toolkit之pt-kill 殺掉mysql查詢或連接的方法
本文主要描述了percona-toolkit中pt-kill的 使用實例 ，及 一些重要參數的介紹,需要的朋友可以參考下2016-04-04
本文主要介紹了深入理解MySQL深分頁慢問題及性能優(yōu)化,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2025-09-09
MySQL事務通過ACID特性保障數據一致性,隔離級別（讀未提交、讀已提交、可重復讀、串行化）平衡并發(fā)性能與數據安全,InnoDB默認使用可重復讀,依賴MVCC避免臟讀和不可重復讀,間隙鎖減少幻讀,需根據場景權衡一致性與性能2025-09-09
MySQL數據庫執(zhí)行效率對程序的執(zhí)行速度有很大的影響，有效的處理優(yōu)化數據庫是非常有用的。尤其是大量數據需要處理的時候2018-03-03
navicat是一款非常強大的數據庫可視化操作軟件,程序開發(fā)中經常會用到navicat,下面這篇文章主要給大家介紹了關于Navicat連接MySQL8.0的正確方法,需要的朋友可以參考下2022-06-06
最近在Windows 2003上的MySQL出現過多次正常運行時無法連接數據庫故障，現象是無法連接數據庫，也無法停止MySQL或重啟MYSQL,由于每次都是草草嘗試各種方法搞定即可本文將詳細介紹解決方法2012-11-11
這篇文章主要為大家詳細介紹了centos7下mysql安裝配置方法圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-04-04
在數據庫管理和數據分析中,單表查詢往往無法滿足復雜的數據查詢需求,因此多表查詢和子查詢成為了解決問題的關鍵技術,MySQL支持多種多表查詢方式,如內連接、外連接（左外連接和右外連接）以及自連接,文中通過代碼介紹的非常詳細,需要的朋友可以參考下2024-10-10
這篇文章主要為大家詳細介紹了mysql 5.7.23安裝配置方法圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2018-09-09