PostgreSQL：如何获取DISTINCT ON筛选后的剩余数据？

针对你的需求，有几种实用的方法可以拿到DISTINCT ON未选中的联系人记录，都是PostgreSQL里常用的方案：

方法一：用窗口函数标记筛选 #

DISTINCT ON本质是取每个website分组里排序后的第一条记录，我们可以用ROW_NUMBER()窗口函数给每个分组的记录编号，然后筛选编号大于1的记录，就是未被选中的部分。

需要注意：窗口函数里的ORDER BY要和你原DISTINCT ON语句里的排序逻辑完全一致，这样才能精准对应原语句选中的第一条记录。

示例代码：

SELECT *
FROM (
    SELECT *,
           -- 按website分组，按你原语句的排序规则给每条记录编号
           ROW_NUMBER() OVER (PARTITION BY website ORDER BY id) AS rn
    FROM contacts
) sub_query
WHERE rn > 1;

把上面的ORDER BY id替换成你原DISTINCT ON语句里的排序字段（比如创建时间create_time）即可。

方法二：用EXCEPT做集合差集 #

直接用全表数据减去DISTINCT ON得到的结果集，剩下的就是未选中的记录。

示例代码：

SELECT * FROM contacts
EXCEPT
-- 这里完全复用你原有的DISTINCT ON语句
SELECT DISTINCT ON (website) * FROM contacts ORDER BY website, id
ORDER BY website;

同样，EXCEPT两边的查询要保证列数、类型一致，且原DISTINCT ON的排序逻辑要带上，避免结果偏差。

方法三：用NOT EXISTS匹配排除 #

通过关联查询，排除掉那些存在于DISTINCT ON结果集中的记录。这种方法适合表有唯一主键（比如id）的场景，匹配更精准。

示例代码：

SELECT c.*
FROM contacts c
WHERE NOT EXISTS (
    SELECT 1
    FROM (
        -- 原DISTINCT ON语句，只取主键和website用于匹配
        SELECT DISTINCT ON (website) id, website
        FROM contacts
        ORDER BY website, id
    ) selected
    WHERE selected.id = c.id AND selected.website = c.website
);

注意事项 #

• DISTINCT ON的结果完全依赖于ORDER BY子句，所以反向查询时一定要保证排序逻辑和原语句一致，否则会拿到错误的未选中记录。
• 如果website字段存在NULL值，以上三种方法都会自动将NULL作为一个独立分组处理，无需额外调整。

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