如何用SQL提取单列中的近两年月末日期及近10天日期？

以下针对主流数据库提供具体实现，核心思路是分别生成两类日期集，再通过UNION ALL合并到同一列中。

MySQL/MariaDB 实现 #

WITH reference_date AS (
    SELECT '2024-01-30' AS ref_date -- 替换为实际参考日期
),
month_end_dates AS (
    SELECT 
        LAST_DAY(DATE_SUB(ref_date, INTERVAL n MONTH)) AS d_fine_mese
    FROM reference_date
    CROSS JOIN (
        SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
        UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
        UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14
        UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
        UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23
    ) AS nums
),
recent_10_days AS (
    SELECT 
        DATE_SUB(ref_date, INTERVAL n DAY) AS d_periodo
    FROM reference_date
    CROSS JOIN (
        SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
        UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
    ) AS nums
)
SELECT d_fine_mese AS combined_date FROM month_end_dates
UNION ALL
SELECT d_periodo AS combined_date FROM recent_10_days
ORDER BY combined_date DESC;

• 用LAST_DAY直接获取指定月份的月末日期，通过数字序列生成近24个月的偏移
• 生成近10天日期时，通过日期减法得到参考日期前1-10天的日期
• UNION ALL保留所有原始行，若需去重（如参考日期为月末时两类日期可能重叠）可替换为UNION

PostgreSQL 实现 #

WITH reference_date AS (
    SELECT '2024-01-30'::DATE AS ref_date
),
month_end_dates AS (
    SELECT 
        (date_trunc('month', ref_date - (n || ' months')::INTERVAL) + INTERVAL '1 month - 1 day')::DATE AS d_fine_mese
    FROM reference_date,
         generate_series(0, 23) AS nums(n)
),
recent_10_days AS (
    SELECT 
        (ref_date - (n || ' days')::INTERVAL)::DATE AS d_periodo
    FROM reference_date,
         generate_series(1, 10) AS nums(n)
)
SELECT d_fine_mese AS combined_date FROM month_end_dates
UNION ALL
SELECT d_periodo AS combined_date FROM recent_10_days
ORDER BY combined_date DESC;

• 利用generate_series快速生成数字序列，无需手动拼接UNION ALL
• 通过date_trunc截断到月份，再计算月末日期

SQL Server 实现 #

DECLARE @ref_date DATE = '2024-01-30';

WITH month_end_dates AS (
    SELECT 
        EOMONTH(DATEADD(MONTH, -n, @ref_date)) AS d_fine_mese
    FROM (
        VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),
               (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
               (20),(21),(22),(23)
    ) AS nums(n)
),
recent_10_days AS (
    SELECT 
        DATEADD(DAY, -n, @ref_date) AS d_periodo
    FROM (
        VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
    ) AS nums(n)
)
SELECT d_fine_mese AS combined_date FROM month_end_dates
UNION ALL
SELECT d_periodo AS combined_date FROM recent_10_days
ORDER BY combined_date DESC;

• 用EOMONTH直接获取月末日期，语法简洁
• 通过VALUES子句生成所需数字序列

内容的提问来源于stack exchange，提问作者Antony Salvo