SQL从/向特定用户获取最后一条消息 [英] SQL get last messages from/to certain user

问题描述

我正在尝试从表中进行SQL查询， -从用户对(以用户36作为发件人或收件人)获取所有成对用户的最后一条消息，并将它们与用户表一起加入以获取名称.我已经设法创建了这样的东西，但仍然想问一下是否还有更简单的方法.有效的解决方案. MySQL版本-5.5.31

    table: messages
    fields: 
    sender_user_id, recipient_user_id, date, text

查询:

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 main
GROUP BY hash;

谢谢. 更新.样本消息表中的数据:

mysql> SELECT id，sender_user_id，receiver_user_id，文本，日期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 |

结果应为id-262、263

解决方案

我正在使用SQL Server 2008，您没有说明正在使用哪个数据库.

根据您提供的信息，查询对于您所需的输出而言似乎过于复杂.这是一个简单的查询，用于获取涉及用户36的所有消息:

SELECT  
       sender.msg_user_name AS sender_user_name
       ,recipient.msg_user_name AS recipient_user_name
       ,msg_date
       ,msg_text

FROM    
       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

WHERE   
       sender_user_id = 36
       OR recipient_user_id = 36

ORDER BY
       msg_date DESC

我不得不更改某些字段名称，就像在SQL Server中一样，您选择的某些名称是保留字.

SQL Fiddle: http://sqlfiddle.com/#!3/b8e88/1

现在，您已经添加了更多信息，并显示了消息表上有一个id字段，您可以使用类似以下的内容(注意:我有SQL Server，因此您可能必须更改对MySQL的查询):

SELECT  sender.msg_user_name AS sender_user_name
       ,recipient.msg_user_name AS recipient_user_name
       ,msg_date
       ,msg_text
FROM    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.id
WHERE   sender_user_id = 36
        OR recipient_user_id = 36
ORDER BY msg_date DESC

查询的FROM部分中的SELECT为发送者/接收者用户ID的每个有序对查找最新消息(基于id，我假设它是一个自动递增的数字).结果将重新连接到Fed_Messages表中，以确保我们正确获取发送者/接收者的名称.

更新的SQL Fiddle: http://sqlfiddle.com/#!3/1f07a/2

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, text

query:

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 main
GROUP 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_text

FROM    
       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

WHERE   
       sender_user_id = 36
       OR recipient_user_id = 36

ORDER BY
       msg_date DESC

I'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/1

EDIT: 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_text
FROM    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.id
WHERE   sender_user_id = 36
        OR recipient_user_id = 36
ORDER BY msg_date DESC

The 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从/向特定用户获取最后一条消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！