MySQL多表关联查询：合并查询并获取单条奖励数据问题

表结构说明 #

• tasks表：存储任务本身（部分任务已完成）；
• reward_categories表：存储所有可能的奖励；
• reward_task_categories表：存储任务与奖励的关联关系；
• todo_tasks表：关联tasks表，记录待办任务及执行人；
• characters表：记录执行待办任务的角色。

需求 #

获取todo_tasks表的所有字段、tasks表的task_name、characters表的character_name，以及每个任务对应的单条奖励（按category_id升序取第一条）。

已有的两个独立可运行查询 #

查询1：关联待办任务与任务表 #

SELECT `tt`.`id`, `t`.`task_name` FROM `todo_tasks` `tt`
    INNER JOIN `tasks` `t` ON `tt`.`task_id` = `t`.`id`;

查询2：获取指定任务的第一条奖励 #

SELECT `category_id`, `category_name` FROM `reward_task_categories` `rtc`
    INNER JOIN `reward_categories` `rc` ON `rtc`.`category_id` = `rc`.`id`
    WHERE `task_id` = 1 ORDER BY `rtc`.`category_id` ASC LIMIT 1;

合并时遇到的问题 #

1. 报错Unknown table 'rc'的查询：

SELECT `tt`.`id`, `t`.`task_name`, `rc`.*, `tt`.`character_id`,
    `c`.`character_name`, `c`.`character_priority` FROM `todo_tasks` `tt`
    INNER JOIN `tasks` `t` ON `tt`.`task_id` = `t`.`id`
    INNER JOIN `characters` `c` ON `tt`.`character_id` = `c`.`id`
    INNER JOIN
        (
            SELECT * FROM `reward_task_categories` `rtci`
            INNER JOIN `reward_categories` `rc` ON `rtci`.`category_id` = `rc`.`id`
            WHERE `task_id` = `tt`.`task_id` ORDER BY `rtci`.`category_id` ASC
            LIMIT 1
        ) `rtc` ON `t`.`task_id` = `rtc`.`task_id`
    LIMIT 1;

2. 调整后仍报相同错误的查询：

SELECT `tt`.`id`, `t`.`task_name`, `rc`.*, `tt`.`character_id`,
    `c`.`character_name`, `c`.`character_priority` FROM `todo_tasks` `tt`
    INNER JOIN `tasks` `t` ON `tt`.`task_id` = `t`.`id`
    INNER JOIN `characters` `c` ON `tt`.`character_id` = `c`.`id`
    INNER JOIN
        (
            SELECT * FROM `reward_task_categories` `rtci`
            INNER JOIN `reward_categories` `rc` ON `rtci`.`category_id` = `rc`.`id`
            WHERE `task_id` = `tt`.`task_id` GROUP BY `rtci`.`task_id`
            ORDER BY `rtci`.`category_id` ASC LIMIT 1
        ) `rtc` ON `t`.`task_id` = `rtc`.`task_id`
    LIMIT 1;

3. 无报错但重复返回任务数据的查询（任务有多条奖励时会生成多条结果）：

SELECT `tt`.`id`, `t`.`task_name`, `rtc`.`category_name`, `tt`.`character_id`,
    `c`.`character_name`, `c`.`character_priority` FROM `todo_tasks` `tt`
    INNER JOIN `tasks` `t` ON `tt`.`task_id` = `t`.`id`
    INNER JOIN `characters` `c` ON `tt`.`character_id` = `c`.`id`
    LEFT JOIN
        (
            SELECT * FROM `reward_task_categories` `rtci`
            INNER JOIN `reward_categories` `rc` ON `rtci`.`category_id` = `rc`.`id`
        ) `rtc` ON `tt`.`task_id` = `rtc`.`task_id`
    LIMIT 1;

方法1：窗口函数（推荐，适用于MySQL 8.0+） #

利用ROW_NUMBER()窗口函数为每个任务的奖励排序，筛选出第一条：

SELECT 
    tt.*,
    t.task_name,
    c.character_name,
    rtc.category_id,
    rtc.category_name
FROM todo_tasks tt
INNER JOIN tasks t ON tt.task_id = t.id
INNER JOIN characters c ON tt.character_id = c.id
LEFT JOIN (
    SELECT 
        rtci.task_id,
        rtci.category_id,
        rc.category_name,
        ROW_NUMBER() OVER (PARTITION BY rtci.task_id ORDER BY rtci.category_id ASC) AS rn
    FROM reward_task_categories rtci
    INNER JOIN reward_categories rc ON rtci.category_id = rc.id
) rtc ON tt.task_id = rtc.task_id AND rtc.rn = 1;

方法2：子查询关联（兼容旧版MySQL） #

先获取每个任务的最小category_id，再关联奖励表获取对应信息：

SELECT 
    tt.*,
    t.task_name,
    c.character_name,
    rc.category_id,
    rc.category_name
FROM todo_tasks tt
INNER JOIN tasks t ON tt.task_id = t.id
INNER JOIN characters c ON tt.character_id = c.id
LEFT JOIN (
    SELECT task_id, MIN(category_id) AS min_category_id
    FROM reward_task_categories
    GROUP BY task_id
) rt_min ON tt.task_id = rt_min.task_id
LEFT JOIN reward_categories rc ON rt_min.min_category_id = rc.id;

错误原因说明 #

1. 前两个查询报错Unknown table 'rc'：

   • 子查询的表别名rc被外层的rtc包裹，外层无法直接引用；
   • 子查询中WHERE task_id = tt.task_id属于关联子查询，结合LIMIT 1时MySQL无法正确识别外层表的关联关系，导致逻辑错误。

2. 第三个查询重复返回数据：

   • 未对奖励数据做筛选，直接关联所有奖励记录，一个任务对应多条奖励时会生成多条结果，不符合单条奖励的需求。

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