SQL：显示DB中的最后一条消息 [英] SQL: Show last messages from DB

问题描述

我有一个SQL查询的问题。 （对不起，我的英语太糟糕了）

我正在开发一个网站的私人消息系统，我有这样的db：

线程

 
 | ID | TITLE | 
 

user_x_thread

 
 | ID | ID_THREAD | ID_USER | 
 

邮件

 
 | ID | ID_THREAD | ID_USER | MESSAGE | TIMESTAMP | 
 

在线程中有一个线程列表

在 user_x_thread 中有每个主题的用户列表。 I.E。在线程＃1中有用户＃2，＃3

最后，在邮件每个线程。

我想显示所有用户@Alex签名的线程，按他们最后的消息排序。

示例

线程

 
 | ID | TITLE | 
 | ＃1 |我和Marta | 
 | ＃2 | Marta和John | 
 | ＃3 |我和约翰| 
 | ＃4 |我，马尔塔和约翰| 
 

user_x_thread

 
 | ID | ID_THREAD | ID_USER | 
 | 1 | ＃1 | Alex | 
 | 2 | ＃1 | Marta | 
 | 3 | ＃2 | Marta | 
 | 4 | ＃2 | John | 
 | 5 | ＃3 | Alex | 
 | 6 | ＃3 | John | 
 | 7 | ＃4 | Alex | 
 | 8 | ＃4 | Marta | 
 | 9 | ＃4 | John | 
 

@ 邮件

 
 | ID | ID_THREAD | ID_USER | MESSAGE | TIMESTAMP | 
 | 1 | ＃1 | Alex | Lorem ipsum | 21:35:45 | 
 | 2 | ＃2 | Marta | Alex无法看到此消息| 21:35:58 | 
 | 3 | ＃3 | John |你好。 | 21:36:10 | 
 | 4 | ＃1 | Marta |演示。 | 21:36:35 | 
 | 5 | ＃4 | John |我喜欢蓝色| 21:36:47 | 
 

结果

Hi Marta，您已登录：

li>

[＃1] Me and Marta（21:36:35）

[＃3] Marta（21:36:10）

解决方案

： -

 从
选择TD.TITLE，MS.MESSAGE，MS.TIMESTAMP THREAD TD 
 JOIN user_x_thread UXT ON TD.ID = UXT.ID_THREAD 
 JOIN消息MS在MS.ID_THREAD = UXT.ID_THREAD 
 WHERE UXT.ID=@userid//say 2或3 
 GROUP BY。 ID，TD.TITLE，MS.MESSAGE，MS.TIMESTAMP 
 ORDER BY MS.TIMESTAMP DESC 
  

@userid是您的登录用户ID：

I've a problem with SQL query. (Sorry, my english is so bad)

I'm developing a private-messages system for a website, and i have db like these:

thread

| ID | TITLE |

user_x_thread

| ID | ID_THREAD | ID_USER |

messages

| ID | ID_THREAD | ID_USER | MESSAGE | TIMESTAMP |

In thread there is a list of threads

In user_x_thread there is a list of user for each threads. I.E. in thread #1 there are user #2, #3

Finally, in messages there is a list of messages sended for each thread.

I would like to show all threads where user @Alex are signed, ordered by their last messages.

EXAMPLE

thread

| ID |        TITLE       |
| #1 |    Me and Marta    |
| #2 |   Marta and John   |
| #3 |     Me and John    |
| #4 | Me, Marta and John |

user_x_thread

| ID | ID_THREAD | ID_USER |
|  1 |     #1    |  Alex   |
|  2 |     #1    |  Marta  |
|  3 |     #2    |  Marta  |
|  4 |     #2    |  John   |
|  5 |     #3    |  Alex   |
|  6 |     #3    |  John   |
|  7 |     #4    |  Alex   |
|  8 |     #4    |  Marta  |
|  9 |     #4    |  John   |

@messages

| ID | ID_THREAD | ID_USER  |            MESSAGE          | TIMESTAMP |
| 1  |     #1    |   Alex   |          Lorem ipsum        |  21:35:45 |
| 2  |     #2    |   Marta  | Alex can't see this message |  21:35:58 |
| 3  |     #3    |   John   |           Hello.            |  21:36:10 |
| 4  |     #1    |   Marta  |            Demo.            |  21:36:35 |
| 5  |     #4    |   John   |        I like blue          |  21:36:47 |

RESULT

Hi Marta, you are signed in: (ordered by last message received)

• [#4] Me, Marta and John (21:36:47)

• [#1] Me and Marta (21:36:35)

• [#3] Me and Marta (21:36:10)

解决方案

you can try like this :-

SELECT TD.TITLE,MS.MESSAGE,MS.TIMESTAMP from 
THREAD TD
JOIN user_x_thread UXT ON TD.ID=UXT.ID_THREAD
JOIN Messages MS ON MS.ID_THREAD=UXT.ID_THREAD
WHERE UXT.ID=@userid//say 2 or 3 
GROUP BY TD.ID,TD.TITLE,MS.MESSAGE,MS.TIMESTAMP
ORDER BY MS.TIMESTAMP DESC 

@userid is your logged user id :

这篇关于SQL：显示DB中的最后一条消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！