PostgreSQL查询需求：按优先级筛选每个物种的单个生物记录

嘿，这需求我熟，给你写个精准的PostgreSQL查询语句，完美匹配你的要求👇

核心需求拆解 #

• 每个物种（用整数类型的organism列标识，确实比文本类型更高效可靠）仅返回一条记录
• 筛选优先级：
  1. 优先选择category = 'reference'的记录
  2. 若该物种没有reference类别的记录，选择你提到的次优先级类别（这里我先假设是representative，你可以按需替换）

最终查询语句 #

WITH ranked_records AS (
    SELECT
        *,
        -- 给每个物种的记录按优先级编号
        ROW_NUMBER() OVER (
            PARTITION BY organism  -- 按整数类型的物种ID分组
            ORDER BY
                -- 定义优先级排序规则
                CASE category
                    WHEN 'reference' THEN 1  -- 最高优先级
                    WHEN 'representative' THEN 2  -- 替换成你的次优先级类别
                    ELSE 3  -- 其他类别优先级最低
                END ASC,
                -- 可选：同优先级下的兜底排序（比如取最新创建的记录）
                created_at DESC
        ) AS rank_num
    FROM
        your_table_name  -- 替换成你的实际数据表名
)
-- 只取每个物种优先级最高的那条记录
SELECT
    organism,
    category,
    genome,
    -- 这里列出你需要返回的其他列
    id,
    created_at
FROM
    ranked_records
WHERE
    rank_num = 1;

关键部分解释 #

• CTE ranked_records：先给每条记录按物种分组，再根据你的优先级规则给每组内的记录编号，优先级最高的记录会被标记为rank_num = 1
• PARTITION BY organism：确保每个整数类型的物种ID单独成组，不会跨物种混排
• ORDER BY CASE ...：这是实现优先级的核心，把reference类别的优先级设为最高（值1），次优先级设为2，其他为3，升序排序让优先级高的排在前面
• 兜底排序条件：如果同一个物种有多个同优先级的记录（比如多个reference），created_at DESC会帮你取最新的那条，你可以换成id DESC或者其他符合你业务逻辑的字段

额外提示 #

用整数类型的organism列确实是更优选择：整数的分组、排序性能远优于文本类型，还能避免文本可能出现的大小写不一致、拼写错误等问题，完全贴合你的要求。

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