如何用单条SQL查询获取指定日期区间内新增/关闭的记录

我需要在SQL中获取表内指定日期区间内新增或关闭的记录，当前通过在不同日期间执行EXCEPT命令再合并结果的方式实现，现咨询是否可通过单条查询达成预期输出。

原始数据表 #

预期输出 #

当前实现代码 #

SELECT column1, 'Added' AS Status FROM mytable WHERE date = '2023-02-03' 
EXCEPT 
SELECT column1 FROM mytable WHERE date = '2023-02-02'
UNION
SELECT column1, 'Closed' AS Status FROM mytable WHERE date = '2023-02-02' 
EXCEPT 
SELECT column1 FROM mytable WHERE date = '2023-02-03'

单条查询解决方案 #

当然可以用单条查询实现，下面提供两种常用方法：

方法1：自连接对比相邻日期 #

通过表自连接直接对比当前日期和前一天的记录差异，筛选新增和关闭状态：

WITH daily_unique AS (
    -- 提取每日唯一的Column1记录，避免同日期重复值干扰判断
    SELECT DISTINCT column1, date FROM mytable
)
-- 新增记录：当前日期存在，前一天不存在
SELECT 
    du.column1,
    du.date,
    'Added' AS Status
FROM daily_unique du
LEFT JOIN daily_unique prev_du 
    ON du.column1 = prev_du.column1 
    AND prev_du.date = DATEADD(day, -1, du.date)
WHERE prev_du.column1 IS NULL

UNION ALL

-- 关闭记录：前一天存在，当前日期不存在
SELECT 
    prev_du.column1,
    DATEADD(day, 1, prev_du.date) AS date,
    'Closed' AS Status
FROM daily_unique du
RIGHT JOIN daily_unique prev_du 
    ON du.column1 = prev_du.column1 
    AND du.date = DATEADD(day, 1, prev_du.date)
WHERE du.column1 IS NULL

ORDER BY date, Status DESC;

方法2：窗口函数标记状态 #

用LAG()和LEAD()窗口函数查看前后日期的记录存在情况，判断状态：

WITH daily_unique AS (
    SELECT DISTINCT column1, date FROM mytable
),
date_tracking AS (
    SELECT 
        column1,
        date,
        -- 查看该值上一次出现的日期
        LAG(date) OVER (PARTITION BY column1 ORDER BY date) AS last_appearance,
        -- 查看该值下一次出现的日期
        LEAD(date) OVER (PARTITION BY column1 ORDER BY date) AS next_appearance
    FROM daily_unique
)
-- 筛选新增记录：第一次出现，或间隔一天以上再次出现
SELECT 
    column1,
    date,
    'Added' AS Status
FROM date_tracking
WHERE last_appearance IS NULL 
   OR DATEADD(day, 1, last_appearance) <> date

UNION ALL

-- 筛选关闭记录：最后一次出现，或间隔一天以上不再出现
SELECT 
    column1,
    DATEADD(day, 1, date) AS date,
    'Closed' AS Status
FROM date_tracking
WHERE next_appearance IS NULL 
   OR DATEADD(day, 1, date) <> next_appearance

ORDER BY date, Status DESC;

注意事项 #

• 两个方法都先做了去重处理，避免原表同日期重复值导致状态判断错误；
• DATEADD是SQL Server语法，若用MySQL可替换为DATE_SUB(date, INTERVAL 1 DAY)/DATE_ADD(date, INTERVAL 1 DAY)，其他数据库请对应调整日期计算函数；
• 最终结果按日期排序，Added状态优先展示，与预期输出一致。

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