如何实现带条件的distinct on查询？PostgreSQL数据筛选需求

需求说明 #

• 针对users表按name分组处理：
  • 若分组内存在age < 17的记录，保留该分组所有数据
  • 若分组内所有记录age > 17，随机选取该分组的一条记录

原表数据 #

正确SQL实现 #

WITH name_group_check AS (
    SELECT 
        name,
        BOOL_OR(age < 17) AS has_under_17
    FROM users
    GROUP BY name
)
SELECT u.*
FROM users u
JOIN name_group_check ngc ON u.name = ngc.name
WHERE ngc.has_under_17 = true
UNION ALL
SELECT DISTINCT ON (u.name) u.*
FROM users u
JOIN name_group_check ngc ON u.name = ngc.name
WHERE ngc.has_under_17 = false
ORDER BY u.name;

逻辑拆解 #

1. 分组预检查：通过CTEname_group_check按name分组，用BOOL_OR函数标记每个分组是否存在年龄小于17的用户。
2. 保留全量数据：关联原表和预检查结果，筛选出has_under_17 = true的分组，返回所有记录。
3. 分组去重：关联原表和预检查结果，筛选出has_under_17 = false的分组，用DISTINCT ON(name)随机选取一条记录（若需固定选取规则，可在ORDER BY中追加字段，比如ORDER BY u.name, u.age DESC可选取该分组年龄最大的记录）。
4. 合并结果：用UNION ALL合并两部分数据，最后按name排序。

执行结果示例 #

（注：Sam的记录可能是id=2或4，具体取决于数据库默认排序，如需固定结果可调整DISTINCT ON后的排序规则）

原SQL问题说明 #

你尝试的SQL不符合PostgreSQL语法，DISTINCT ON是查询级别的关键字，不能嵌套在CASE表达式的分支中，因此无法按该方式实现逻辑。

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