mysql - 如何将另一个表加入到此查询中

Question

这是我第一次看到如此复杂的查询。下面是查询，我需要在 profile_id 上加入另一个名为“profiles”的表。

表结构

原始查询

SELECT a.*
    FROM messages a
    INNER JOIN
    (
        SELECT message_replay_id, MAX(message_id) AS latest_message
        FROM messages
        WHERE message_from = '1' || message_to = '1'
        GROUP BY message_replay_id 
    ) b
    ON a.message_replay_id = b.message_replay_id
    AND a.message_id = b.latest_message
    ORDER BY a.message_id DESC

因此，它将加入 message_to = profile_id。

这就是我目前正在尝试的

SELECT a.* FROM messages a
    INNER JOIN(SELECT message_replay_id, MAX(message_id) AS latest_message FROM messages WHERE message_from = '1' || message_to = '1' GROUP BY message_replay_id ) b
    ON a.message_replay_id = b.message_replay_id AND a.message_id = b.latest_message
    INNER JOIN(SELECT * AS latest_profile FROM PROFILES WHERE profile_id = b.latest_message ) c
    ON a.message_replay_id = b.message_replay_id AND a.message_id = b.latest_message
ORDER BY a.message_id DESC

错误消息

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS latest_profile FROM profiles WHERE profile_id = b.latest_message ) c
    ON a.m' at line 4

帮帮我，我该如何加入另一张 table 。

Answer 1

您不能在 from 子句中的子查询内引用表别名。相反:

SELECT m.*
FROM messages m INNER JOIN
     (SELECT message_replay_id, MAX(message_id) AS latest_message
      FROM messages
      WHERE message_from = '1' || message_to = '1'
      GROUP BY message_replay_id
     ) mm
     ON m.message_replay_id = mm.message_replay_id AND
        m.message_id = mm.latest_message INNER JOIN
     profiles p 
     ON p.profile_id = mm.latest_message
ORDER BY mm.message_id DESC;

此外:

• as 与 * 不合适(一般情况下)。它只能重命名一列。
• 您无需在前两个表之间重复联接条件。
• profiles 的子查询是不必要的，并且会损害 MySQL 的性能。
• 如果表别名是表缩写，查询会更容易理解。