显示我收到消息的前十个联系人 [英] Show the ten first contacts that I have recieved message

问题描述

我想创建一个 Sql 语句，显示我收到的消息的前十个联系人以及他们最近发送的消息和时间.表列是messageId、messageBody、fromUser、toUser、timeStamp，表称为messages.数据库是Mysql，Java是语言.但我希望这发生在一个单一的 sql 语句中.

I want to create Sql statement that show the ten first contacts that I have recieved message from along with their latest sent message and time. The table columns is messageId, messageBody, fromUser, toUser, timeStamp and table is called messages. The database is Mysql and Java is the language. But I want this to happen in one single sql statement.

推荐答案

我将显示前十个联系人"解释为提交消息的前 10 个人.

I'm interpreting "show the ten first contacts" as the first 10 people that submitted messages.

Select M2.fromUser
    , LastMessage.messageBody
    , LastMessage.timeStamp
From    (
        Select M1.fromUser
            , Min(M1.timeStamp) As FirstMessage
            , Max(M1.messageId) As LastMessageId
        From messages As M1
        Where M1.toUser = 'my username'
        Group By M1.fromUser
        Order By Min(M1.timeStamp) Asc
        Limit 10
        ) As M2
    Join messages As LastMessage
        On LastMessage.messageId = M2.LastMessageId

这篇关于显示我收到消息的前十个联系人的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！