SQLite使用SELECT语句对比相邻行筛选去重唯一行的方法

你的需求完全可以用单条SQLite语句实现，你目前写的语句功能是正确的，但并非最优方案，存在效率更高、更简洁的写法。

现有写法的不足 #

你当前用的UNION+NOT IN的方案存在两个可优化点：

• 需要多次扫描数据表（T1、T2、子查询的T3各扫一次），数据量大时性能下降明显
• NOT IN在处理存在NULL值的数据集时容易出现非预期结果，虽然你这个场景下没问题，但通用性较差

最优实现方案（SQLite 3.25.0及以上版本推荐） #

SQLite 3.25.0版本开始支持窗口函数，用ROW_NUMBER()窗口函数可以实现仅扫描一次表就完成筛选，性能最优，代码也更简洁易读：

SELECT name, fk1, fk2, watch
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY fk2 
            ORDER BY CASE WHEN fk1 IS NOT NULL THEN 0 ELSE 1 END
        ) AS row_rank
    FROM my_table
    WHERE watch = 1
) AS temp_table
WHERE row_rank = 1;

逻辑说明 #

1. 内层查询先筛选出所有WATCH = 1的行
2. 按FK2分组，同组内FK1非空的行排序优先级高于FK1为NULL的行
3. 外层查询只取每个分组排序后的第一行，刚好符合需求：同FK2有非空FK1就保留非空行，没有就保留NULL行

低版本SQLite兼容方案 #

如果你的SQLite版本不支持窗口函数，可以用LEFT JOIN替代原有的UNION方案，性能也优于你当前的写法：

SELECT DISTINCT t1.name, t1.fk1, t1.fk2, t1.watch
FROM my_table t1
LEFT JOIN my_table t2 
  ON t1.fk2 = t2.fk2 
  AND t2.watch = 1 
  AND t2.fk1 IS NOT NULL
WHERE t1.watch = 1
AND (t1.fk1 IS NOT NULL OR t2.fk2 IS NULL);

逻辑说明 #

1. 同FK2的行做关联，关联条件是存在同FK2、WATCH=1且FK1非空的行
2. 保留两种行：FK1非空的行，或者FK1为NULL且没有关联到同FK2非空FK1的行

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