快捷導(dǎo)航

MySQL中使用CTE獲取時間段數(shù)據(jù)的技巧分享

更新時間：2024年08月21日 10:46:12 作者：nbsaas-boot

在數(shù)據(jù)庫操作中,獲取特定時間段的數(shù)據(jù)是一項(xiàng)常見任務(wù),MySQL自從8.0版本開始支持CTE（公共表表達(dá)式）,使得我們可以更加靈活和高效地處理時間段數(shù)據(jù),本文小編介紹了MySQL中使用CTE獲取時間段數(shù)據(jù)的技巧分享,需要的朋友可以參考下

引言

在數(shù)據(jù)庫操作中，獲取特定時間段的數(shù)據(jù)是一項(xiàng)常見任務(wù)。MySQL自從8.0版本開始支持CTE（公共表表達(dá)式），使得我們可以更加靈活和高效地處理時間段數(shù)據(jù)。本文將介紹如何使用CTE獲取最近十二個月、最近十二周、最近四個季度，以及如何獲取十二個月前的月第一天和十二周前的周第一天。

1. 獲取最近十二個月

當(dāng)你需要獲取最近十二個月的月份數(shù)據(jù)時，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') 獲取當(dāng)前的年份和月份。
使用遞歸的方式生成從當(dāng)前月往前推的十二個月。
最后，按年份和月份倒序排列，以確保最近的月份排在前面。

2. 獲取最近十二周

如果你需要獲取最近十二周的周數(shù)據(jù)，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) 獲取當(dāng)前日期所在的年份和周數(shù)。
遞歸地從當(dāng)前周開始，往前推十二周。
結(jié)果按年份和周數(shù)倒序排列，顯示最近的十二周。

3. 獲取最近四個季度

對于季度數(shù)據(jù)的處理，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()) 獲取當(dāng)前的年份和季度。
遞歸生成最近四個季度的數(shù)據(jù)，確保季度的轉(zhuǎn)換是正確的（例如，從第一季度到上一年的第四季度）。
最終結(jié)果按年份和季度倒序排列。

4. 獲取十二個月前的月第一天

當(dāng)你需要獲取十二個月前的月份的第一天，可以使用以下查詢：

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) 計(jì)算十二周前的日期。
使用 WEEKDAY() 函數(shù)計(jì)算該日期對應(yīng)的周幾（0表示周一，6表示周日）。
最后通過減去這個天數(shù)，得到十二周前的周一。

結(jié)論

MySQL中的CTE為我們提供了強(qiáng)大的工具來處理復(fù)雜的時間段計(jì)算。通過使用CTE，你可以輕松地生成最近十二個月、十二周、四個季度的數(shù)據(jù)，還可以精確地獲取特定時間段的第一天。掌握這些技巧，可以讓你的時間管理和數(shù)據(jù)處理更加高效、精確，為你的數(shù)據(jù)庫操作帶來更大的靈活性和可控性。

以上就是MySQL中使用CTE獲取時間段數(shù)據(jù)的技巧分享的詳細(xì)內(nèi)容，更多關(guān)于MySQL CTE獲取時間段數(shù)據(jù)的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: