SQL需求：计算上月遗留至当月1日的未结记录数量

针对你需要统计截至当月1日，上月遗留未结记录数量的需求，我来拆解逻辑并给出具体的SQL写法：

核心逻辑梳理 #

要确定某一月1日的遗留未结记录，需要同时满足两个条件：

1. 记录的录入日期不晚于上月最后一天（也就是在统计当月1日之前已经存在）
2. 记录在统计当月1日当天仍未关闭——要么从未关闭（CloseDate为空），要么关闭日期在当月1日之后

单月份统计（以SQL Server为例） #

如果你只需要统计某个特定月份（比如2023年10月1日）的遗留数，可以用这段代码：

DECLARE @target_date DATE = '2023-10-01'; -- 指定要统计的当月1日
DECLARE @last_month_end DATE = DATEADD(DAY, -1, @target_date); -- 计算上月最后一天

SELECT
    @target_date AS StatDate,
    COUNT(*) AS OpenCount,
    STRING_AGG(ID, ', ') AS RelatedIDs -- 可选：展示对应的记录ID
FROM records
WHERE
    DateEntered <= @last_month_end
    AND (CloseDate IS NULL OR CloseDate >= @target_date);

如果是MySQL环境，函数语法略有调整：

SET @target_date = '2023-10-01';
SET @last_month_end = DATE_SUB(@target_date, INTERVAL 1 DAY);

SELECT
    @target_date AS StatDate,
    COUNT(*) AS OpenCount,
    GROUP_CONCAT(ID SEPARATOR ', ') AS RelatedIDs
FROM records
WHERE
    DateEntered <= @last_month_end
    AND (CloseDate IS NULL OR CloseDate >= @target_date);

批量统计所有月份 #

如果需要一次性统计所有有记录的月份的当月1日遗留数，可以用CTE生成所有需要统计的日期，再关联数据：

SQL Server版本 #

-- 生成所有需要统计的当月1日（覆盖所有有录入/关闭记录的月份）
WITH AllStatDates AS (
    SELECT DISTINCT
        DATEFROMPARTS(YEAR(DateEntered), MONTH(DateEntered), 1) AS StatDate
    FROM records
    UNION
    SELECT DISTINCT
        DATEFROMPARTS(YEAR(CloseDate), MONTH(CloseDate), 1) AS StatDate
    FROM records
    WHERE CloseDate IS NOT NULL
)
SELECT
    s.StatDate,
    COUNT(r.ID) AS OpenCount,
    STRING_AGG(r.ID, ', ') AS RelatedIDs
FROM AllStatDates s
LEFT JOIN records r ON
    r.DateEntered <= DATEADD(DAY, -1, s.StatDate)
    AND (r.CloseDate IS NULL OR r.CloseDate >= s.StatDate)
GROUP BY s.StatDate
ORDER BY s.StatDate;

MySQL版本 #

WITH AllStatDates AS (
    SELECT DISTINCT
        DATE_FORMAT(DateEntered, '%Y-%m-01') AS StatDate
    FROM records
    UNION
    SELECT DISTINCT
        DATE_FORMAT(CloseDate, '%Y-%m-01') AS StatDate
    FROM records
    WHERE CloseDate IS NOT NULL
)
SELECT
    s.StatDate,
    COUNT(r.ID) AS OpenCount,
    GROUP_CONCAT(r.ID SEPARATOR ', ') AS RelatedIDs
FROM AllStatDates s
LEFT JOIN records r ON
    r.DateEntered <= DATE_SUB(s.StatDate, INTERVAL 1 DAY)
    AND (r.CloseDate IS NULL OR r.CloseDate >= s.StatDate)
GROUP BY s.StatDate
ORDER BY s.StatDate;

注意事项 #

• 关于关闭日期的边界：如果业务定义“当月1日当天关闭的记录不算遗留”，请把条件里的CloseDate >= @target_date改成CloseDate > @target_date
• RelatedIDs字段是可选的，用来展示对应记录ID，不需要的话可以直接删掉这个字段

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