简单的mysql查询按特定顺序显示结果 [英] Simple mysql query displaying results in specific order

问题描述

我已创建2个表格消息和对话。

I have created 2 table messages and Conversation.

对话表将为正在发送和接收消息的两个用户创建唯一的ID。

Conversation table will create an unique id for two users who is sending and receiving messages.

邮件表将包含用户邮件的所有信息。

Messages table will contains all info of user messages.

会话表

conversation_id | user_id1 | user_id2 |last_message_id
      1              5          1           4
      2              5          2           5

message_id  | conversation_id | sender_id |  message |  created_time
   1           1                   5          MSG A     2012-06-10 00:20:04
   2           1                   5          MSG B     2012-06-10 00:20:21
   3           1                   5          MSG C     2012-06-10 00:21:09
   4           1                   1          MSG D     2012-06-10 00:22:23
   5           2                   5          MSG E     2012-06-10 00:25:16

我想要的输出：

sender_id | message | created_time
   5        MSG E     2012-06-10 00:25:16
   1        MSG D     2012-06-10 00:22:23

我尝试了：

SELECT * FROM conversation c, messages m 
WHERE c.user_id1='$userid' OR c.user_id2='$userid'
AND c.last_message_id=m.message_id
ORDER BY created_time DESC

但此查询未显示正确的结果。

But this query is not displaying right result.

我们需要类似以下的内容吗？

Do we need something like following to group by as well?

SELECT * FROM TABLE
WHERE CONDITION
(SELECT MAX(created_time)
FROM TABLE
GROUP BY SOMEFEILD)
order by time desc

推荐答案

尝试如下更改您的查询

SELECT * FROM conversation c, messages m 
WHERE (c.user_id1='$userid' OR c.user_id2='$userid')
AND c.last_message_id=m.message_id
ORDER BY created_time DESC

这篇关于简单的mysql查询按特定顺序显示结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！