如何构建这个SQL查询：选择谈话的最后一封邮件 [英] How to build this SQL Query : Select conversation with last message

问题描述

您好，我在SQL中的新手，这种要求有很大的头痛：

Hello I am a newbie in sql and have great headache with such a demand:

有2表：会话和消息

CONVERSATION: _id INTEGER, description TEXT
MESSAGE: _id INTEGER, message TEXT, conversation_id INTEGER

每个会话可以包含多个消息。

each conversation could contain multi messages.

我需要把所有的谈话与他们的最后消息（最大_id）：
Conversation1 +最后那次谈话的消息（如果存在，那么每列空）。
例如：

I need to get all conversation with their last message(max _id): Conversation1 + Last Message of that Conversation(if not exist then each column null). for example

 _id       description     message_id              message             conversation_id

 1         conv 1            7             "last message in conv 1           1 

 2         conv 2            12             "last message in conv 2"         2

 3         conv 3           null                   null                     null  
(this conversation has no message)

 ......

15         conv 15          212            "last message in conv 15"         15

我怎么能写查询？

How could i write the query?

顺便说一句，如果我需要在Android的SQLite的查询，我需要查询的原始SQL或可以使用一些帮助类此查询？

btw, if i need to query in android with sqlite, do i need to query a raw sql or can use some help class for this query?

感谢大家谁把时间花在我的问题！

Thanks everyone who spend time on my question!

---------------

－－－－－－－－－－－－－－－

我只是写一个SQL查询可我请Profies看看？

I just wrote a sql query could i please Profies have a look?

选择对话。*，message._id作为与Message_ID，message.message，message.conversation_id
从谈话LEFT JOIN消息上CONVERSATION._ID = message.conversation_id
通过message.conversation_id组已经message._id = MAX（message.id）

SELECT conversation.*, message._id as message_id, message.message, message.conversation_id FROM conversation LEFT JOIN message on CONVERSATION._ID=message.conversation_id group by message.conversation_id having message._id = max(message.id)

==============================

==============================

最后我做一个sql语句下面和它的作品：

finally i make a sql statement as below and it works:

SELECT * FROM谈话左加入（选择最大（_id），由conversation_id消息组conversation_id）上conversation._id = conversation_id

select * from conversation left join (select max(_id), conversation_id from message group by conversation_id) on conversation._id = conversation_id

感谢大家谁帮我：）

推荐答案

此方法使用相关子查询来获得最后一条消息的ID。然后，它加入了回的消息表，以获得更多的信息：

This approach uses a correlated subquery to get the last message id. It then joins that back to the messages table to get the additional information:

select c.*, m._id, m.message
from (select c.*,
             (select max(_id) as maxid
              from message m
              where c._id = m.conversationid
             ) as messageid
      from conversation c
     ) c join
     message m
     on c.messageid = m._id;

这篇关于如何构建这个SQL查询：选择谈话的最后一封邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！