不要使用老式的逗号分隔表列表，尤其是不要与更“现代”的联接语法混合使用。您还在选择列表中跳过了app_id，并为max_created_at进行了联接。unread_count的相关子查询是不必要的，因为您可以将其添加到max_created_at子查询中：

SELECT
  `messages`.*,
  `max_created_at`.`unread_count`
FROM `messages`
INNER JOIN (
    SELECT
        `contact_id`,
        `app_id`,
        MAX(`created_at`) AS `latest_created_at`,
        COUNT(IF(`status` = 'sent', 1, NULL)) AS `unread_count`
    FROM `messages`
    GROUP BY `contact_id`, `app_id`
) AS `max_created_at`
    ON `messages`.`contact_id` = `max_created_at`.`contact_id`
    AND `messages`.`app_id` = `max_created_at`.`app_id`
    AND `messages`.`created_at` = `max_created_at`.`latest_created_at`
INNER JOIN `apps`
    ON `messages`.`app_id` = `apps`.`id` 
INNER JOIN `contacts`
    ON `messages`.`contact_id` = `contacts`.`id` 
WHERE `messages`.`app_id` = 1;

我觉得你只是漏掉了一个表名的反勾号-

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`                    -- I have added here
INNER JOIN `contacts` AS `contacts` ON `messages`.`contact_id` = `contacts`.`id`     -- I have added here
WHERE
  `messages`.`app_id` = 1
  AND `messages`.`contact_id` = max_created_at.`contact_id`
  AND `messages`.`created_at` = max_created_at.latest_created_at