如何进行连接，其中最后一个表被限制为1或0？

Question

我有两张桌子: chat_table和message_table。

我想要创建视图，它包含来自聊天台+消息的行和来自message_table的时间(如果有)。

CREATE VIEW chat_view AS  
SELECT 
    chat_table._id AS _id,
    chat_table.chat_id AS chat_id,
    chat_table.title AS title,
    chat_table.title_lower AS title_lower,
    chat_table.interlocutor_username AS interlocutor_username,
    chat_table.interlocutor_lastname AS interlocutor_lastname,
    chat_table.interlocutor_name AS interlocutor_name,
    chat_table.interlocutor_username_lower AS interlocutor_username_lower,
    chat_table.interlocutor_lastname_lower AS interlocutor_lastname_lower,
    chat_table.interlocutor_photo AS interlocutor_photo,
    chat_table.status AS status,
    chat_table.type AS type,
    chat_table.client_id AS client_id,
    message_table.message AS message,
    message_table.message_lower AS message_lower,
    message_table.time AS time  
FROM 
    chat_table 
INNER JOIN 
    message_table 
ON 
    chat_table.chat_id = message_table.chat_id 
INNER JOIN  
    (  SELECT  chat_id,
     MAX( time ) AS time  FROM message_table GROUP BY chat_id ) b 
ON 
    b.chat_id = message_table.chat_id AND message_table.time = b.time

当前，此状态创建了一个视图，该视图使用最后一条消息显示聊天，忽略没有消息的聊天。

不带短信的聊天很热吗？

编辑

关于左联接

CREATE VIEW chat_view5 AS  SELECT chat_table._id AS _id, chat_table.chat_id AS chat_id, chat_table.title AS title, chat_table.title_lower AS title_lower, chat_table.interlocutor_username AS interlocutor_username, chat_table.interlocutor_lastname AS interlocutor_lastname, chat_table.interlocutor_name AS interlocutor_name, chat_table.interlocutor_username_lower AS interlocutor_username_lower, chat_table.interlocutor_lastname_lower AS interlocutor_lastname_lower, chat_table.interlocutor_photo AS interlocutor_photo, chat_table.status AS status, chat_table.type AS type, chat_table.client_id AS client_id, message_table.message AS message, message_table.message_lower AS message_lower, message_table.time AS time  FROM chat_table LEFT JOIN message_table ON chat_table.chat_id = message_table.chat_id LEFT JOIN  (  SELECT  chat_id,  MAX( time ) AS time  FROM message_table GROUP BY chat_id ) b ON b.chat_id = message_table.chat_id AND message_table.time = b.time

​

​

Answer 1

我最后

CREATE VIEW chat_view AS  SELECT chat_table._id AS _id, chat_table.photo AS photo, chat_table.interlocutor_id AS interlocutor_id, chat_table.chat_id AS view_chat_id, chat_table.title AS title, chat_table.title_lower AS title_lower, chat_table.interlocutor_username AS interlocutor_username, chat_table.interlocutor_lastname AS interlocutor_lastname, chat_table.interlocutor_name AS interlocutor_name, chat_table.interlocutor_username_lower AS interlocutor_username_lower, chat_table.interlocutor_lastname_lower AS interlocutor_lastname_lower, chat_table.interlocutor_photo AS interlocutor_photo, chat_table.status AS status, chat_table.type AS type, chat_table.client_id AS client_id, message_table.message AS message, message_table.message_lower AS message_lower,  MAX ( message_table.time )  AS time  FROM chat_table LEFT JOIN message_table ON chat_table.chat_id = message_table.chat_id GROUP BY chat_table.chat_id

Answer 2

您可以使用LEFT JOIN，但需要执行两次：

FROM chat_table LEFT JOIN 
     message_table 
     ON  chat_table.chat_id = message_table.chat_id LEFT JOIN  
     (SELECT  chat_id, MAX( time ) AS time
      FROM message_table
      GROUP BY chat_id
     ) b 
     ON b.chat_id = message_table.chat_id AND message_table.time = b.time

请注意，大多数数据库支持ANSI标准ROW_NUMBER()函数，它提供了获取最新消息的更简单的方法。