PostgreSQL跨多表联合搜索后合并排序分页的实现方法

你可以通过 UNION ALL 拼接两个表的符合条件的查询结果，统一排序后再分页，具体实现如下：

注意事项 #

• 两个联合查询返回的列数量、对应数据类型必须完全一致，我们额外增加 source_type 字段标识数据来源，方便业务层区分返回结果属于蛋糕还是烘焙师
• 原SQL中的cake_tyoe为笔误，已修正为cake_type；另外PostgreSQL的ILIKE本身就是大小写不敏感匹配，不需要额外加lower()转换的冗余条件
• 必须合并结果后再统一排序分页，不能分别给两个表加limit/offset，否则会导致全局排序和分页结果错误

完整SQL示例 #

-- 外层统一排序+分页
SELECT * FROM (
  -- 查cakes表符合条件的数据
  SELECT
    'cake' AS source_type,
    id AS item_id,
    cake_type AS item_name,
    views
  FROM cakes
  WHERE cake_type ILIKE :searchString

  UNION ALL -- 不需要去重用UNION ALL性能远高于UNION

  -- 查bakers表符合条件的数据
  SELECT
    'baker' AS source_type,
    id AS item_id,
    name AS item_name,
    views
  FROM bakers
  WHERE name ILIKE :searchString
) AS union_result
ORDER BY views DESC -- 全局按浏览量倒序
LIMIT 10 -- 分页参数
OFFSET 5

参数说明 #

• :searchString 为你的模糊搜索参数，建议前后拼上%实现任意位置匹配，比如%芝士%
• 业务层可以通过source_type字段判断当前行数据属于蛋糕还是烘焙师，分别处理业务逻辑

如果模糊查询性能不佳，可以给cakes.cake_type和bakers.name字段安装pg_trgm扩展后创建GIN索引，大幅提升ILIKE模糊查询速度。

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