PostgreSQL一对多关联表聚合查询的分页实现及去重问题

一、添加分页功能 #

在PostgreSQL中用LIMIT和OFFSET实现分页非常直接，但**必须配合ORDER BY**保证分页结果的稳定性（否则每次查询的结果顺序可能不一致，导致分页数据混乱）。

修改后的完整SQL（以每页10条为例，第一页OFFSET 0，第二页OFFSET 10，以此类推）：

SELECT 
    t1."entityId", 
    t1."typeId", 
    t1."createdAt", 
    t1."itemCount" AS entity_type_items,
    t2.total_items_shipped AS shipped_entity_type_items
FROM 
    public.entity_type t1
JOIN 
    (SELECT 
         "entityId", 
         "typeId", 
         SUM("itemsShipped") AS total_items_shipped
     FROM 
         public.shipment_entity_type
     WHERE 
         "deletedAt" IS NULL
     GROUP BY 
         "entityId", "typeId") t2 ON t1."entityId" = t2."entityId" 
                                  AND t1."typeId" = t2."typeId"
WHERE 
    t1."itemCount" <> t2.total_items_shipped
-- 添加排序规则，确保分页结果稳定
ORDER BY t1."entityId", t1."typeId"
-- 分页参数：LIMIT指定每页条数，OFFSET指定跳过的记录数
LIMIT 10 OFFSET 0;

说明：

• LIMIT 10：限制每页返回10条记录
• OFFSET n：n为跳过的记录数，比如第2页用OFFSET 10，第3页用OFFSET 20
• ORDER BY：必须指定，建议选择唯一或稳定的排序字段（如entityId+typeId组合），避免分页时出现重复或遗漏数据

二、处理entityId重复问题 #

原查询中同一entityId对应不同typeId会作为独立行返回，这是因为关联和分组都基于entityId+typeId的组合。如果需要优化展示效果，可通过以下两种方式调整：

方式1：合并同一entityId的多typeId数据 #

如果要把同一entityId的不同typeId合并为一行展示，可使用聚合函数（如array_agg、string_agg）合并相关字段：

SELECT 
    t1."entityId",
    -- 将同一entityId的所有typeId合并为数组
    array_agg(DISTINCT t1."typeId") AS type_ids,
    -- 取同一entityId下最早的创建时间（可按需改为MAX取最晚）
    MIN(t1."createdAt") AS earliest_created_at,
    -- 汇总同一entityId的总物品数量
    SUM(t1."itemCount") AS total_entity_items,
    -- 汇总同一entityId的总发货数量
    SUM(t2.total_items_shipped) AS total_shipped_items
FROM 
    public.entity_type t1
JOIN 
    (SELECT 
         "entityId", 
         "typeId", 
         SUM("itemsShipped") AS total_items_shipped
     FROM 
         public.shipment_entity_type
     WHERE 
         "deletedAt" IS NULL
     GROUP BY 
         "entityId", "typeId") t2 ON t1."entityId" = t2."entityId" 
                                  AND t1."typeId" = t2."typeId"
WHERE 
    t1."itemCount" <> t2.total_items_shipped
GROUP BY t1."entityId"
-- 添加排序和分页
ORDER BY t1."entityId"
LIMIT 10 OFFSET 0;

方式2：筛选出存在多typeId的entityId记录 #

如果只是要找出那些对应多个typeId的entityId，可通过窗口函数标记重复项：

SELECT *
FROM (
    SELECT 
        t1."entityId", 
        t1."typeId", 
        t1."createdAt", 
        t1."itemCount" AS entity_type_items,
        t2.total_items_shipped AS shipped_entity_type_items,
        -- 标记同一entityId下的记录数量
        COUNT(*) OVER (PARTITION BY t1."entityId") AS type_count
    FROM 
        public.entity_type t1
    JOIN 
        (SELECT 
             "entityId", 
             "typeId", 
             SUM("itemsShipped") AS total_items_shipped
         FROM 
             public.shipment_entity_type
         WHERE 
             "deletedAt" IS NULL
         GROUP BY 
             "entityId", "typeId") t2 ON t1."entityId" = t2."entityId" 
                                      AND t1."typeId" = t2."typeId"
    WHERE 
        t1."itemCount" <> t2.total_items_shipped
) sub
WHERE sub.type_count > 1
ORDER BY sub."entityId", sub."typeId"
LIMIT 10 OFFSET 0;

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