MySQL复杂查询需要建议

Question

我有一个表格如下:

 id | sender      | receiver    | time 
  1 | felix@a.com | lea@a.com   | 2014-07-04 22:50:16    
  2 | felix@a.com | lea@a.com   | 2014-07-04 22:51:20    
  3 | felix@a.com | mia@a.com   | 2014-07-04 22:51:41    
  4 | mia@a.com   | felix@a.com | 2014-07-04 22:52:45    
  5 | mia@a.com   | felix@a.com | 2014-07-04 22:52:58
  6 | lea@a.com   | felix@a.com | 2014-07-04 22:53:33
  7 | felix@a.com | mia@a.com   | 2014-07-04 22:55:53

我想获取具有按时间降序排列的“felix@a.com”条目的不同发件人/收件人。因此，输出将如下所示

 id | sender      | receiver    | time
  6 | lea@a.com   | felix@a.com | 2014-07-04 22:53:33
  7 | felix@a.com | mia@a.com   | 2014-07-04 22:55:53

我正在尝试这个，但无法正常工作

SELECT * 
FROM (
SELECT * 
FROM  `message` 
ORDER BY  `time` DESC
) AS  `message` 
WHERE (
message.sender =  'felix@a.com'
OR message.receiver =  'felix@a.com'
)
GROUP BY message.receiver, message.sender

请指导我实现目标的正确方法。

Answer 1

DROP TABLE my_table;

CREATE TABLE my_table
(id INT NOT NULL PRIMARY KEY
,sender VARCHAR(20) NOT NULL     
,receiver VARCHAR(20) NOT NULL    
,time DATETIME NOT NULL
);

INSERT INTO my_table VALUES
(1,'felix@a.com','lea@a.com','2014-07-04 22:50:16'),
(2,'felix@a.com','lea@a.com','2014-07-04 22:51:20'),
(3,'felix@a.com','mia@a.com','2014-07-04 22:51:41'),
(4,'mia@a.com','felix@a.com','2014-07-04 22:52:45'),
(5,'mia@a.com','felix@a.com','2014-07-04 22:52:58'),
(6,'lea@a.com','felix@a.com','2014-07-04 22:53:33'),
(7,'felix@a.com','mia@a.com','2014-07-04 22:55:53');

(SELECT * FROM my_table WHERE sender = 'felix@a.com' ORDER BY time DESC LIMIT 1)
UNION
(SELECT * FROM my_table WHERE receiver = 'felix@a.com' ORDER BY time DESC LIMIT 1);
+----+-------------+-------------+---------------------+
| id | sender      | receiver    | time                |
+----+-------------+-------------+---------------------+
|  7 | felix@a.com | mia@a.com   | 2014-07-04 22:55:53 |
|  6 | lea@a.com   | felix@a.com | 2014-07-04 22:53:33 |
+----+-------------+-------------+---------------------+