MySQL报错#1054：on子句中'messages.app_id'列不存在，求排查

报错信息 #

MySQL said: Documentation

#1054 - Unknown column 'messages.app_id' in 'on clause'

原查询语句 #

SELECT
  `messages`.*,
  (
    SELECT
      COUNT(*)
    FROM
      messages AS message
    WHERE
      message.app_id = `messages`.`app_id`
      AND message.contact_id = `messages`.`contact_id`
      AND message.status = "sent"
  ) AS `unread_count`
FROM
  `messages`,
  (
    SELECT
      `contact_id`,
      max(`created_at`) as latest_created_at
    FROM
      `messages`
    GROUP BY
      `contact_id`, `app_id`
  ) AS `max_created_at`
INNER JOIN `apps` AS `apps` ON `messages.app_id` = `apps`.`id` 
INNER JOIN `contacts` AS `contacts` ON `messages.contact_id` = `contacts`.`id` 
WHERE
  `messages`.`app_id` = 1
  AND `messages`.`contact_id` = max_created_at.`contact_id`
  AND `messages`.`created_at` = max_created_at.latest_created_at

问题具体位置及原因 #

1. 混合表连接语法导致作用域异常
   同时使用了旧式逗号分隔表（FROM messages, max_created_at）和JOIN关键字连接表，MySQL中这两种语法的优先级不同：逗号分隔的表属于独立查询组，后续JOIN子句无法直接引用该组内的messages表，因此触发字段不存在的报错。

2. 子查询逻辑存在漏洞
   子查询max_created_at的GROUP BY包含app_id，但SELECT列表未返回该字段，后续WHERE条件仅关联contact_id和created_at，会导致不同app_id下的同联系人记录被错误匹配，逻辑不严谨。

修正后的查询语句 #

SELECT
  `messages`.*,
  (
    SELECT
      COUNT(*)
    FROM
      messages AS message
    WHERE
      message.app_id = `messages`.`app_id`
      AND message.contact_id = `messages`.`contact_id`
      AND message.status = "sent"
  ) AS `unread_count`
FROM
  `messages`
INNER JOIN (
    SELECT
      `contact_id`,
      `app_id`,
      max(`created_at`) as latest_created_at
    FROM
      `messages`
    GROUP BY
      `contact_id`, `app_id`
  ) AS `max_created_at`
  ON `messages`.`contact_id` = max_created_at.`contact_id`
  AND `messages`.`created_at` = max_created_at.latest_created_at
  AND `messages`.`app_id` = max_created_at.`app_id`
INNER JOIN `apps` AS `apps` 
  ON `messages`.`app_id` = `apps`.`id` 
INNER JOIN `contacts` AS `contacts` 
  ON `messages`.`contact_id` = `contacts`.`id` 
WHERE
  `messages`.`app_id` = 1

修正说明 #

• 统一使用JOIN语法连接所有表，确保messages表在所有JOIN子句中处于可见作用域。
• 子查询max_created_at添加app_id到SELECT列表，并在JOIN条件中关联app_id，避免跨应用的联系人记录错误匹配。

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