如何用单条SQL查询指定回溯月数内各地点月末物品存量

要实现无需UNION的批量查询，核心是先生成目标范围内的所有月末日期，再结合所有地点做全量组合，最后关联物品表统计数量。以下是针对主流数据库的实现方案：

方案思路 #

1. 生成目标月末日期集合：通过递归方式生成从输入日期所在月末开始，往前指定月数的所有月末日期。
2. 提取所有唯一地点：确保每个月末都能列出所有存在的地点，避免遗漏无物品的地点。
3. 全量组合+统计：将日期和地点交叉组合，左连接物品表统计每个月末前各地点的物品数量（无物品时显示0）。

SQL Server 实现代码 #

DECLARE @InputDate DATE = '2022-06-30';
DECLARE @BackMonths INT = 3;

WITH MonthEnds AS (
    -- 初始行：输入日期所在月的月末
    SELECT EOMONTH(@InputDate) AS MonthEnd
    UNION ALL
    -- 递归生成往前的月末，直到达到回溯月数
    SELECT EOMONTH(DATEADD(MONTH, -1, MonthEnd))
    FROM MonthEnds
    WHERE DATEDIFF(MONTH, EOMONTH(DATEADD(MONTH, -1, MonthEnd)), @InputDate) < @BackMonths
),
Locations AS (
    -- 获取所有唯一地点
    SELECT DISTINCT Location
    FROM Items
)
-- 交叉连接日期与地点，左连接统计物品数量
SELECT 
    me.MonthEnd,
    l.Location,
    COUNT(i.ItemID) AS Items
FROM MonthEnds me
CROSS JOIN Locations l
LEFT JOIN Items i 
    ON l.Location = i.Location 
    AND i.DateAdded <= me.MonthEnd
GROUP BY me.MonthEnd, l.Location
ORDER BY me.MonthEnd DESC, l.Location;

MySQL 8.0+ 实现代码 #

SET @InputDate = '2022-06-30';
SET @BackMonths = 3;

WITH RECURSIVE MonthEnds AS (
    -- 初始行：输入日期所在月的月末
    SELECT LAST_DAY(@InputDate) AS MonthEnd
    UNION ALL
    -- 递归生成往前的月末，直到达到回溯月数
    SELECT LAST_DAY(DATE_SUB(MonthEnd, INTERVAL 1 MONTH))
    FROM MonthEnds
    WHERE TIMESTAMPDIFF(MONTH, LAST_DAY(DATE_SUB(MonthEnd, INTERVAL 1 MONTH)), @InputDate) < @BackMonths
),
Locations AS (
    -- 获取所有唯一地点
    SELECT DISTINCT Location
    FROM Items
)
-- 交叉连接日期与地点，左连接统计物品数量
SELECT 
    me.MonthEnd,
    l.Location,
    COUNT(i.ItemID) AS Items
FROM MonthEnds me
CROSS JOIN Locations l
LEFT JOIN Items i 
    ON l.Location = i.Location 
    AND i.DateAdded <= me.MonthEnd
GROUP BY me.MonthEnd, l.Location
ORDER BY me.MonthEnd DESC, l.Location;

关键说明 #

• 若输入日期不是月末，EOMONTH（SQL Server）或LAST_DAY（MySQL）会自动取该月最后一天，不影响统计结果。
• 回溯月数设为0时，仅返回输入日期所在月的月末统计数据。
• 建议给Items.DateAdded字段添加索引，提升大数据量下的查询性能。

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