我该怎么做这个SQL查询？

I have some messages and a current user.

我有一些消息和当前用户。

Messages have an id, users have an id.

消息具有id,用户具有id。

I want to get a list of (unique) user ids where:

我想获得(唯一的)用户ID列表,其中:

The first user id is for the user who sent the message to the current user with the highest message id and all the message ids for the last user id are lower than that of any other user who sent the current user a message.

第一个用户ID是针对将消息发送给具有最高消息ID的当前用户的用户,并且最后一个用户id的所有消息ID都低于向当前用户发送消息的任何其他用户的消息ID。

So far I am able to get a list of users who sent the current user a message with:

到目前为止,我能够获得向当前用户发送消息的用户列表:

"SELECT sender_id AS messgr_id FROM   messages 
    WHERE  receiver_id = '$curr_id' 
UNION SELECT receiver_id AS messgr_id FROM messages
    WHERE  sender_id = '$curr_id' ";

but I am stuck as to the best way to order it in the way i want (i am learning SQL as i do this and so im no expert :))

但我被困在以我想要的方式订购它的最佳方式(我正在学习SQL,因为我这样做,所以我没有专家:))

Thanks!

2 个解决方案

#1

something like this should do the trick

这样的事情应该可以解决问题

SELECT * 
FROM ( 
    SELECT sender_id AS messgr_id FROM   messages 
        WHERE  receiver_id = '$curr_id' 
    UNION SELECT receiver_id AS messgr_id FROM messages
        WHERE  sender_id = '$curr_id'
) T1
GROUP BY messgr_id
ORDER BY messgr_id DESC

That will give you a list of user ids with duplicates eliminated, and sorted high-to-low.

这将为您提供一个已删除重复项的用户ID列表,并从高到低排序。

Update:

Re-reading your question, it sounds like you're looking for a list of user id's ordered by message id -- which isn't currently shown in your example. Assuming that your messages table has an id column (in addition to receiver_id and sender_id), here's how you would do that:

重新阅读您的问题,听起来您正在寻找按消息ID排序的用户ID列表 - 当前未在您的示例中显示。假设你的消息表有一个id列(除了receiver_id和sender_id之外),你可以这样做:

SELECT messgr_id, max(message_id) as max_message_id
FROM ( 
    SELECT sender_id AS messgr_id, id as message_id FROM messages 
        WHERE  receiver_id = '$curr_id' 
    UNION SELECT receiver_id AS messgr_id, id as message_id FROM messages
        WHERE  sender_id = '$curr_id'
) T1
GROUP BY messgr_id
ORDER BY max_message_id DESC

#2

i'm not sure but i think you only want to order your SELECTion?

我不确定但我认为你只想订购你的SELECTion?

"SELECT sender_id AS messgr_id FROM   messages 
    WHERE  receiver_id = '$curr_id'
    ORDER BY sender_id
UNION SELECT receiver_id AS messgr_id FROM messages
    WHERE  sender_id = '$curr_id' 
    ORDER BY sender_id DESC";

#1