mysql - 两列不同的SQL

Question

所以我有一个如下所示的 SQL 表。这个表是一堆人与人之间的短信。我想获取所有存在的线程。这基本上意味着两个人之间的最后一条消息。我该怎么做？

-------------------------------------------------------
| sender_id  |  receiver_id  | message   | time         |
-------------------------------------------------------
| 123        |  456          | hi        | 4/17/2013    | 
--------------------------------------------------------
| 123        |  111          | hi        | 4/18/2013    | 
--------------------------------------------------------
| 123        |  555          | hi        | 4/19/2013    | 
-------------------------------------------------------- 
| 555        |  123          | hi        | 4/20/2013    | 
--------------------------------------------------------
| 444        |  333          | hi        | 4/21/2013    | 
--------------------------------------------------------
| 123        |  555          | hi        | 4/22/2013    | 
--------------------------------------------------------
| 777        |  123          | hi        | 4/23/2013    | 
--------------------------------------------------------

我想获得 user= 123 的响应行，如下所示。请注意 sender_id 和 receiver_id 作为一个整体是如何唯一的。这意味着 joe 发送给 bob 的消息与 bob 发送给 joe 的消息在同一个线程中。

-------------------------------------------------------
| sender_id  |  receiver_id  | message   | time         |
-------------------------------------------------------
| 123        |  456          | hi        | 4/17/2013    | 
--------------------------------------------------------
| 123        |  111          | hi        | 4/18/2013    | 
--------------------------------------------------------
| 123        |  555          | hi        | 4/22/2013    | 
--------------------------------------------------------
| 777        |  123          | hi        | 4/23/2013    | 
--------------------------------------------------------

Answer 1

一个更易于阅读的版本，它正确处理日期排序(如问题中所示)并利用索引:

SELECT sender_id, receiver_id, message, time FROM
(
SELECT sender_id, receiver_id, message, time
FROM myTable 
WHERE sender_id = 123 OR receiver_id = 123
ORDER BY time DESC
) a
GROUP BY (CASE WHEN sender_id = 123 THEN receiver_id
   ELSE sender_id END);

SQL fiddle .