如何在一对多关联中获取指定数量含CV的集合及对应文档

原查询的问题在于：它先关联所有表并过滤出CV文档后直接取前2行，这只会返回任意2条CV文档（大概率来自同一个collection），完全不符合“获取前2个包含CV的collection及其所有CV文档”的需求。

核心思路 #

先筛选出前2个包含CV文档的collection，再关联获取这些collection下的所有CV文档。以下是不同数据库环境的实现方案：

方案1：支持ANSI FETCH FIRST的数据库（PostgreSQL 12+、Oracle 12c+、DB2等） #

SELECT c.*, d.*, m.*
FROM (
    -- 先筛选出前2个含CV文档的collection，去重确保每个collection只出现一次
    SELECT c.id, c.*
    FROM collection c
    JOIN document d ON d.collection_id = c.id
    JOIN model m ON d.model_id = m.id
    WHERE m.type = 'cv'
    GROUP BY c.id, c.*
    -- 若需按特定顺序选collection，添加ORDER BY，比如ORDER BY c.create_time DESC
    FETCH FIRST 2 ROWS ONLY
) AS top_collections
-- 关联获取这些collection下的所有CV文档
JOIN document d ON d.collection_id = top_collections.id
JOIN model m ON d.model_id = m.id
WHERE m.type = 'cv';

方案2：MySQL/MariaDB（使用LIMIT） #

SELECT c.*, d.*, m.*
FROM (
    SELECT c.id, c.*
    FROM collection c
    JOIN document d ON d.collection_id = c.id
    JOIN model m ON d.model_id = m.id
    WHERE m.type = 'cv'
    GROUP BY c.id, c.*
    -- 按需添加ORDER BY
    LIMIT 2
) AS top_collections
JOIN document d ON d.collection_id = top_collections.id
JOIN model m ON d.model_id = m.id
WHERE m.type = 'cv';

方案3：Oracle 11g及更早版本（使用ROWNUM） #

SELECT c.*, d.*, m.*
FROM (
    SELECT c.id, c.*
    FROM collection c
    JOIN document d ON d.collection_id = c.id
    JOIN model m ON d.model_id = m.id
    WHERE m.type = 'cv'
    GROUP BY c.id, c.*
) AS collections
JOIN document d ON d.collection_id = collections.id
JOIN model m ON d.model_id = m.id
WHERE m.type = 'cv'
AND collections.id IN (
    SELECT id FROM (
        SELECT c.id
        FROM collection c
        JOIN document d ON d.collection_id = c.id
        JOIN model m ON d.model_id = m.id
        WHERE m.type = 'cv'
        GROUP BY c.id
        -- 按需添加ORDER BY
    ) WHERE ROWNUM <= 2
);

关键说明 #

• 子查询中的GROUP BY c.id, c.*用于去重，避免同一个collection多次进入候选列表。
• 如果需要按特定规则（如创建时间、名称）选择前2个collection，必须在子查询中添加ORDER BY子句，否则数据库会返回任意符合条件的2个collection。

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