我正在创建一个小型消息传递平台，并且有三个表：
`msg_conversations`
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
+-------+---------------------+------+-----+---------+----------------+
`msg_participants`
+------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| convo_id | int(10) unsigned | NO | MUL | NULL | |
| prof_id | int(10) unsigned | NO | MUL | NULL | |
| prof_type | tinyint(3) unsigned | NO | | NULL | |
| is_visible | tinyint(3) unsigned | NO | | NULL | |
+------------+---------------------+------+-----+---------+----------------+
`msg_messages`
+----------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| participant_id | int(10) unsigned | NO | MUL | NULL | |
| message | text | NO | | NULL | |
| time_cre | int(11) | NO | | NULL | |
+----------------+---------------------+------+-----+---------+----------------+
我想返回最新消息（通过msg_messages.time_cre）以及参与者所在的每个会话的convo_id。
我的逻辑如下：1）获取用户正在参与的所有对话ID（由msg_participants.prof_type和msg_participants.prof_id定义）。 2）根据每个对话ID获取每个参与者。 3）返回每个对话中的最后一条消息（按msg_messages.time_cre排序）。该功能应类似于Facebook如何返回对话列表以及每个对话中最后一条消息的预览。
到目前为止，这是我的代码：
SELECT msg_participants.convo_id,
msg_messages.id,
msg_participants.prof_id,
msg_participants.prof_type,
SUBSTRING(msg_messages.message,1,50) as message,
msg_messages.time_cre
FROM msg_participants,msg_messages
WHERE
msg_participants.id IN
(SELECT participant_id FROM
(SELECT * FROM (SELECT msg_messages.participant_id,msg_participants.convo_id FROM msg_messages,msg_participants WHERE msg_messages.participant_id=msg_participants.id AND participant_id IN
(SELECT id FROM msg_participants WHERE convo_id IN (SELECT convo_id FROM msg_participants WHERE prof_id=PROFILE_ID_HERE AND prof_type=PROFILE_TYPE_HERE))
ORDER BY time_cre DESC) AS tbl1
GROUP BY tbl1.convo_id) AS tbl2)
AND msg_messages.id IN
(SELECT id FROM (SELECT * FROM (SELECT msg_messages.id,msg_participants.convo_id FROM msg_messages,msg_participants WHERE msg_messages.participant_id=msg_participants.id AND participant_id IN
(SELECT id FROM msg_participants WHERE convo_id IN (SELECT convo_id FROM msg_participants WHERE prof_id=PROFILE_ID_HERE AND prof_type=PROFILE_TYPE_HERE))
ORDER BY time_cre DESC) AS tbl3
GROUP BY tbl3.convo_id) AS tbl4)
我知道我的代码多么丑陋/ hacky，但是我已经重写了很多遍，以至于我只想要可以工作的东西。该代码是...关闭。但是它返回msg_participants.id X msg_messages.id的笛卡尔积。对于它为什么返回笛卡尔积，这对我来说很有意义，但是我不确定如何使此查询执行我想要的操作。
谢谢！
最佳答案
您可以使用not exists为给定的msg_messages从participant_id中选择最新行。
select mp.convo_id, mm.id, mp.prof_id, mp.prof_type, mm.message, mm.time_cre
from msg_participants mp
join msg_messages mm on mp.id = mm.participant_id
where mp.prof_id = PROF_ID_HERE
and mp.prof_type = PROF_TYPE_HERE
and not exists (
select 1 from msg_messages mm2
where mm2.participant_id = mm.participant_id
and mm2.time_cre > mm.time_cre
)