如何限制JOIN后的SQL查询：每条news记录最多关联2张images

现有两张数据表：news表存储新闻信息，images表存储关联图片，通过new_id关联news表的id字段。当前的INNER JOIN查询会返回所有关联图片，以下是实现每个新闻最多返回2条图片记录的解决方案：

通用窗口函数解法（支持MySQL 8.0+、PostgreSQL、SQL Server等） #

利用ROW_NUMBER()窗口函数对每个新闻下的图片进行分组编号，再筛选编号≤2的记录：

SELECT id, title, main_image, services, file_name
FROM (
    SELECT 
        n.id, 
        n.title, 
        n.main_image, 
        n.services, 
        i.file_name,
        -- 按新闻ID分组，给每组内的图片排序并编号
        ROW_NUMBER() OVER (PARTITION BY n.id ORDER BY i.id) AS img_row_num
    FROM news AS n
    INNER JOIN images AS i ON n.id = i.new_id
) AS img_with_row
WHERE img_row_num <= 2;

说明 #

• PARTITION BY n.id：按新闻ID分组，确保每个新闻的图片单独编号
• ORDER BY i.id：按图片ID排序（可根据需求替换为file_name等其他字段）
• 外层查询筛选编号≤2的记录，实现每个新闻最多返回2张图片

兼容MySQL 5.x的解法（无窗口函数支持时使用） #

通过用户变量追踪当前新闻和图片序号，实现分组编号：

SELECT 
    n.id, 
    n.title, 
    n.main_image, 
    n.services, 
    i.file_name
FROM news AS n
INNER JOIN (
    SELECT 
        file_name, 
        new_id,
        -- 若当前新闻ID与上一条相同，序号+1，否则重置为1
        @row_num := IF(@current_news = new_id, @row_num + 1, 1) AS img_row_num,
        @current_news := new_id
    FROM images, (SELECT @row_num := 0, @current_news := NULL) AS vars
    ORDER BY new_id, id
) AS i ON n.id = i.new_id
WHERE i.img_row_num <= 2;

说明 #

• 初始化变量@row_num（记录图片序号）和@current_news（记录当前新闻ID）
• 按新闻ID和图片ID排序，确保序号计算逻辑正确
• 最后筛选序号≤2的记录，达到限制数量的目的

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