关联查询及WHERE条件下的SQL分页实现问题

直接对article LEFT JOIN tag的结果加LIMIT会导致分页逻辑错误——因为关联后一篇文章会对应多行（每个标签一行），LIMIT是对行进行截断，而不是对文章进行分页。比如一篇有6个标签的文章会占6行，LIMIT 7只能拿到这篇的全部标签和另一篇的1个标签，本质是分页对象搞错了。

核心解决方案 #

要实现按文章分页，必须先筛选出分页后的文章集合，再关联标签和作者信息，确保LIMIT作用在文章数量上。

1. 不带过滤条件的分页查询 #

用CTE先获取分页后的文章（含作者信息），再关联标签：

WITH paginated_articles AS (
    -- 先分页获取文章和作者信息，LIMIT 2表示每页2篇，OFFSET 0对应第1页
    SELECT a.*, au.*
    FROM article a
    JOIN author au ON a.author_id = au.id
    LIMIT 2 OFFSET 0
)
-- 关联标签，此时每个文章的所有标签都会被完整返回
SELECT pa.*, t.*
FROM paginated_articles pa
LEFT JOIN tag t ON pa.id = t.article_id;

2. 带标签过滤的分页查询 #

如果需要筛选特定标签的文章并分页，先拿到符合条件的文章ID，再分页获取文章信息，最后关联标签：

WITH filtered_article_ids AS (
    -- 筛选出带"TV Show"标签的所有文章ID，用DISTINCT避免重复
    SELECT DISTINCT a.id
    FROM article a
    JOIN tag t ON a.id = t.article_id
    WHERE t.name = "TV Show"
),
paginated_articles AS (
    -- 对筛选后的文章分页，获取文章和作者信息
    SELECT a.*, au.*
    FROM article a
    JOIN author au ON a.author_id = au.id
    JOIN filtered_article_ids fai ON a.id = fai.id
    LIMIT 2 OFFSET 0
)
-- 关联标签，返回完整的文章、作者、标签信息
SELECT pa.*, t.*
FROM paginated_articles pa
LEFT JOIN tag t ON pa.id = t.article_id;

兼容老版本数据库（无CTE支持） #

如果你的数据库不支持CTE（如MySQL 5.6及以前），可以用子查询替代：

不带过滤的子查询版本

SELECT pa.*, t.*
FROM (
    SELECT a.*, au.*
    FROM article a
    JOIN author au ON a.author_id = au.id
    LIMIT 2 OFFSET 0
) pa
LEFT JOIN tag t ON pa.id = t.article_id;

带过滤的子查询版本

SELECT pa.*, t.*
FROM (
    SELECT a.*, au.*
    FROM article a
    JOIN author au ON a.author_id = au.id
    WHERE a.id IN (
        SELECT DISTINCT a2.id
        FROM article a2
        JOIN tag t2 ON a2.id = t2.article_id
        WHERE t2.name = "TV Show"
    )
    LIMIT 2 OFFSET 0
) pa
LEFT JOIN tag t ON pa.id = t.article_id;

关键说明 #

• 所有方案的核心都是先对文章实体分页，再关联标签，确保分页逻辑基于文章数量而非关联后的行数。
• 使用DISTINCT是为了避免筛选标签时，同一文章因多个符合条件的标签被重复选中，导致分页出现重复文章。

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