SQLite优化查询：保留一条Done状态重复请求，清除其余重复项

针对你的需求——在存在重复（同一user/start_date/end_date）且至少有一条Done状态的请求组中，保留一条Done记录，返回其余所有重复请求的id——我推荐以下两种高效方案，同时附上性能优化的关键步骤：

一、优先使用窗口函数（SQLite 3.25.0+） #

窗口函数是当前最简洁高效的实现方式，只需一次表扫描即可完成分组、排序和过滤：

WITH grouped_requests AS (
    SELECT 
        id,
        -- 按请求组分组，给组内记录排序：Done状态优先，再按id升序
        ROW_NUMBER() OVER (
            PARTITION BY user, start_date, end_date 
            ORDER BY CASE WHEN status = 'Done' THEN 0 ELSE 1 END, id
        ) AS row_num,
        -- 标记当前组是否存在Done状态的记录
        MAX(CASE WHEN status = 'Done' THEN 1 ELSE 0 END) OVER (
            PARTITION BY user, start_date, end_date
        ) AS has_done_record
    FROM request
)
SELECT id
FROM grouped_requests
-- 只筛选有Done记录的组，且排除组内第一条Done记录（row_num=1）
WHERE has_done_record = 1 AND row_num > 1
ORDER BY id;

逻辑说明： #

1. PARTITION BY user, start_date, end_date 将数据按重复请求组拆分；
2. ORDER BY CASE WHEN status = 'Done' THEN 0 ELSE 1 END, id 确保每个组内Done记录排在最前面，且优先保留id最小的那条Done（如果想保留最大id，把id改成id DESC即可）；
3. has_done_record 标记该组是否符合「至少有一条Done」的筛选条件；
4. 最后过滤出符合条件的组中，除了第一条Done之外的所有记录id。

二、兼容旧版SQLite的关联查询方案 #

如果你的SQLite版本低于3.25.0（不支持窗口函数），可以用以下关联查询实现：

-- 第一步：找出每个有Done记录的组中，要保留的那条Done记录id（这里取最小id）
WITH keepable_done_ids AS (
    SELECT MIN(id) AS keep_id
    FROM request
    WHERE status = 'Done'
    GROUP BY user, start_date, end_date
),
-- 第二步：找出所有属于这些符合条件组的记录id
target_group_records AS (
    SELECT r.id
    FROM request r
    WHERE EXISTS (
        SELECT 1 
        FROM keepable_done_ids k
        JOIN request r2 ON r2.id = k.keep_id
        WHERE r2.user = r.user 
          AND r2.start_date = r.start_date 
          AND r2.end_date = r.end_date
    )
)
-- 第三步：排除掉要保留的那条Done记录，返回其余id
SELECT id
FROM target_group_records
WHERE id NOT IN (SELECT keep_id FROM keepable_done_ids)
ORDER BY id;

三、关键性能优化：添加组合索引 #

原查询速度慢的核心原因大概率是缺少合适的索引，导致SQLite需要全表扫描来分组和筛选。建议创建以下组合索引：

CREATE INDEX idx_request_user_dates_status ON request(user, start_date, end_date, status);

这个索引可以让SQLite快速定位到重复请求组，同时直接从索引中获取status信息，避免回表查询，大幅提升查询效率。

效果验证 #

针对你给出的示例数据集，两种方案都会返回：454, 457, 603, 605（或604，取决于你选择保留的Done记录id）, 607, 608，完全符合预期。

内容的提问来源于stack exchange，提问作者Danilo Souza Morães