如何在一对多关联查询中获取指定数量主表记录并关联子表数据

针对你遇到的问题，核心是要先锁定主表的前X条记录，再关联子表，避免关联后的多行数据干扰主表分页结果。以下是几种更优雅的实现方式：

方案1：先分页主表，再关联子表（最通用） #

通过CTE先获取前X条人员记录，再与子表关联，同时将子表的过滤条件放在JOIN ON子句中，保证主表记录不会因子表无匹配数据而丢失：

WITH top_persons AS (
    SELECT *
    FROM PERSON_TABLE
    ORDER BY person_id -- 必须指定排序字段，确保分页结果稳定
    OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY
)
SELECT p.*, f.*
FROM top_persons p
LEFT JOIN FILE_TABLE f 
    ON p.person_id = f.person_id
    AND f.file_type = 'PDF' -- 子表过滤条件
ORDER BY p.person_id, f.file_id;

如果需要过滤主表数据，直接在top_persons的WHERE子句中添加即可。

方案2：用窗口函数标记主表排名，再关联 #

对于不支持FETCH NEXT语法的数据库，可使用ROW_NUMBER()窗口函数标记主表记录的排名，筛选前X条后再关联子表：

WITH ranked_persons AS (
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY person_id) AS rn
    FROM PERSON_TABLE
    -- 主表过滤条件可添加在此处
)
SELECT rp.*, f.*
FROM ranked_persons rp
LEFT JOIN FILE_TABLE f 
    ON rp.person_id = f.person_id
    AND f.create_time > '2024-01-01' -- 子表过滤条件
WHERE rp.rn <= 2
ORDER BY rp.rn, f.file_id;

方案3：使用LATERAL JOIN（适用于PostgreSQL/SQL Server等） #

LATERAL JOIN允许针对主表的每一行，单独查询符合条件的子表数据，既保证主表行数为前X条，又能灵活过滤子表：

SELECT p.*, f.*
FROM (
    SELECT *
    FROM PERSON_TABLE
    ORDER BY person_id
    OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY
) p
LEFT JOIN LATERAL (
    SELECT *
    FROM FILE_TABLE f
    WHERE f.person_id = p.person_id
      AND f.size > 1024 -- 子表过滤条件
) f ON true
ORDER BY p.person_id, f.file_id;

为什么直接关联后分页无效？ #

直接关联后，一对多关系会生成多行数据（如Eric对应2个文件就会有2行），此时OFFSET 0 FETCH NEXT 2 ROWS会取这2行，而不是2个不同的人员记录。必须先完成主表的分页筛选，再关联子表才能得到正确结果。

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