SQL实现各分类下每月最后日期查询 无需辅助日期表

首先明确：不需要预建存储月份起止的辅助表，可以通过递归CTE动态生成连续月份序列完成计算，适配MySQL 8.0+、PostgreSQL、SQL Server等支持递归语法的主流数据库。

实现逻辑 #

• 先将原表中dd/mm/yy格式的字符串日期转为标准日期类型，同时标记每条记录所属月份的第一天
• 提取原表中所有不重复的分类（item），以及数据集覆盖的最早、最晚月份边界
• 通过递归CTE动态生成边界范围内的所有连续月份，和所有分类做笛卡尔积，补全所有「分类-月份」组合（包含原表无数据的月份）
• 左关联原表数据，按「分类-月份」分组取组内最大日期，即为该分类对应月份的最后日期，无数据的月份返回-，格式化后即可得到目标结果

参考代码 #

假设原表名为item_records，MySQL环境下的实现代码如下：

WITH RECURSIVE
base_conv AS (
    SELECT
        item,
        STR_TO_DATE(`date`, '%d/%m/%y') AS record_date,
        DATE_FORMAT(STR_TO_DATE(`date`, '%d/%m/%y'), '%Y-%m-01') AS month_start
    FROM item_records
),
all_items AS (
    SELECT DISTINCT item FROM base_conv
),
date_range AS (
    SELECT
        MIN(month_start) AS start_month,
        MAX(month_start) AS end_month
    FROM base_conv
),
month_series AS (
    SELECT start_month AS month FROM date_range
    UNION ALL
    SELECT DATE_ADD(month, INTERVAL 1 MONTH)
    FROM month_series
    WHERE month < (SELECT end_month FROM date_range)
),
full_scaffold AS (
    SELECT i.item, m.month
    FROM all_items i
    CROSS JOIN month_series m
)
SELECT
    s.item,
    DATE_FORMAT(s.month, '%b-%Y') AS monthYear,
    COALESCE(DATE_FORMAT(MAX(b.record_date), '%e/%c/%y'), '-') AS monthYear_lastDate
FROM full_scaffold s
LEFT JOIN base_conv b
    ON s.item = b.item AND s.month = b.month_start
GROUP BY s.item, s.month
ORDER BY s.item, s.month;

适配说明 #

• 如果使用PostgreSQL，将日期转换函数替换为TO_DATE(xxx, 'DD/MM/YY')、日期加减替换为month + INTERVAL '1 month'、月份格式化替换为TO_CHAR(xxx, 'Mon-YYYY')即可
• 如果使用SQL Server，将日期转换替换为CONVERT(DATE, xxx, 3)，日期加减替换为DATEADD(MONTH, 1, month)即可
• 如果是不支持递归CTE的旧版数据库（如MySQL 5.7），可以用内置数字序列（如information_schema.COLUMNS表的自增序号）动态生成连续月份，同样不需要创建持久化的辅助表。

内容的提问来源于stack exchange，提问作者Devarshi Goswami