I'm trying to make SQL query from table, - get last messages from all pairs of users with user 36 as sender or recipient, and join them with users table to get names. I've managed to create something like this, but still want to ask if there is more simple | efficient solution.Mysql version - 5.5.31 table: messages fields: sender_user_id, recipient_user_id, date, textquery: SELECT *FROM (SELECT (CASE sender_user_id > recipient_user_id WHEN true THEN CONCAT(recipient_user_id, '|', sender_user_id) WHEN false THEN CONCAT(sender_user_id, '|', recipient_user_id) END) as hash, sender_user_id, recipient_user_id, date, text, u.first_name FROM fed_messages LEFT JOIN fed_users as u ON ((fed_messages.sender_user_id = 36 AND fed_messages.recipient_user_id = u.id) OR (fed_messages.recipient_user_id = 36 AND fed_messages.sender_user_id = u.id)) WHERE sender_user_id = 36 OR recipient_user_id = 36 ORDER BY date DESC) as mainGROUP BY hash;Thanks.Upd. Data from sample messages table:mysql> SELECT id, sender_user_id, recipient_user_id, text, date FROM federation.fed_messages WHERE id > 257;-----+----------------+-------------------+-----------------+---------------------+| id | sender_user_id | recipient_user_id | text | date |+-----+----------------+-------------------+-----------------+---------------------+| 258 | 40 | 36 | and one more | 2013-06-06 10:57:17 || 259 | 36 | 38 | another message | 2013-06-06 11:03:49 || 260 | 38 | 36 | some mes | 2013-06-06 12:29:33 || 261 | 38 | 36 | message | 2013-06-06 12:29:53 || 262 | 36 | 38 | message | 2013-06-06 12:47:26 || 263 | 36 | 40 | some message | 2013-06-10 16:22:46 |The result should be with ids - 262, 263 解决方案 I'm using SQL Server 2008, you don't say which database you are using.From the information you have supplied your query seems overly complex for the output you require. Here's a simple query to get all the messages involving user 36:SELECT sender.msg_user_name AS sender_user_name ,recipient.msg_user_name AS recipient_user_name ,msg_date ,msg_textFROM dbo.Fed_Messages INNER JOIN dbo.Fed_User AS sender ON sender.msg_user_id = sender_user_id INNER JOIN dbo.Fed_User AS recipient ON recipient.msg_user_id = recipient_user_idWHERE sender_user_id = 36 OR recipient_user_id = 36ORDER BY msg_date DESCI've had to change some field names as in SQL Server some of the names you have chosen are reserved words.SQL Fiddle: http://sqlfiddle.com/#!3/b8e88/1EDIT:Now you've added some more information, and shown there is an id field on the message table, you could use something like this (note: I have SQL Server so you will probably have to change the query for MySQL):SELECT sender.msg_user_name AS sender_user_name ,recipient.msg_user_name AS recipient_user_name ,msg_date ,msg_textFROM dbo.Fed_Messages INNER JOIN dbo.Fed_User AS sender ON sender.msg_user_id = sender_user_id INNER JOIN dbo.Fed_User AS recipient ON recipient.msg_user_id = recipient_user_id INNER JOIN ( SELECT MAX(id) AS most_recent_message_id FROM dbo.Fed_Messages GROUP BY CASE WHEN sender_user_id > recipient_user_id THEN recipient_user_id ELSE sender_user_id END -- low_id ,CASE WHEN sender_user_id < recipient_user_id THEN recipient_user_id ELSE sender_user_id END -- high_id ) T ON T.most_recent_message_id = dbo.Fed_Messages.idWHERE sender_user_id = 36 OR recipient_user_id = 36ORDER BY msg_date DESCThe SELECT in the FROM part of the query finds the most recent message (based on the id, I'm assuming it's an auto incrementing number) for each ordered pair of sender/recipient user id's. The result of that is rejoined to the Fed_Messages table to ensure we get the names for sender/receiver correct.Updated SQL Fiddle: http://sqlfiddle.com/#!3/1f07a/2 这篇关于SQL从/向特定用户获取最后一条消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！
