PostgreSQL中按指定优先级从XML数据中筛选单条记录的实现求助

嗨，我来帮你搞定这个问题！你现在的需求很明确：同一个publication_id下，优先选load-source为patent-office的XML记录，如果没有这个来源，再选docdb的，而且绝对不能同时返回两条记录对吧？

你之前的查询之所以会返回两条，是因为只是做了来源的条件过滤，但没对同ID的记录做优先级排序+取唯一的处理。这里给你一个用PostgreSQL窗口函数的靠谱实现，非常适合这种按优先级筛选的场景：

首先是完整的SQL语句，直接就能用：

WITH ranked_xml AS (
    SELECT 
        publication_id,
        xml,
        -- 给不同来源分配优先级：patent-office排最前（行号1），docdb次之
        ROW_NUMBER() OVER (
            PARTITION BY publication_id 
            ORDER BY 
                CASE 
                    WHEN xpath('//abstract/@load-source', xml) = 'patent-office' THEN 1
                    WHEN xpath('//abstract/@load-source', xml) = 'docdb' THEN 2
                    ELSE 3 -- 其他未知来源优先级最低，最后考虑
                END
        ) AS rn
    FROM xml
    -- 这里可以去掉WHERE条件，一次性处理所有publication_id的记录
    WHERE publication_id = 162382550
)
SELECT publication_id, xml
FROM ranked_xml
WHERE rn = 1;

我给你拆解下这个语句的逻辑：

1. 先用CTE（公共表表达式）ranked_xml给每个publication_id分组，给组内的每条记录按我们要的优先级标行号：patent-office的行号是1，docdb是2，其他来源是3。
2. 外层查询只取行号为1的记录，这样每个publication_id就只会返回一条优先级最高的记录，完全符合你的要求。

如果你的需求是直接提取XML里的abstract文本内容，而不是返回整个XML字段，那可以把外层查询改成这样：

SELECT 
    publication_id,
    -- 提取abstract下p标签的文本内容
    xpath('//abstract/p/text()', xml) AS abstract_content
FROM ranked_xml
WHERE rn = 1;

这样就能直接拿到你需要的文本啦，不用再自己处理XML结构。

备注：内容来源于stack exchange，提问作者Despe1990