mysql - SQLITE 查询连接两个具有不同条件的相同表

Question

我有一个名为 messages 的 SQL 表，其中包含如下详细信息；

id, sender, recipient, message, timestamp

"1","10204456572654160","10208819391147662","Hi.. : What are you up to?","1459541723279"
"2","10204456572654160","10208819391147662","Got my test message?","1459541749818"
"3","10208819391147662","10204456572654160","This is my message","1459611679108"
"4","10208819391147662","10204456572654160","And another message","1459611735455"
"5","10204456572654160","10208819391147662","And I reply like this","1459611758570"
"6","10153775515332771","10208819391147662","It's me.. Syco !!","1459611900348"
"7","10153775515332771","10208819391147662","You there?","1459611900350"
"8","10208819391147662","10153775515332771","Yes.. What's upp..","1459611900380"
"9","10204464403169266","10208819391147662","How're you doin?","1459612000666"

现在，我想将一个值传递给我的函数例如10208819391147662 并显示结果，如以下列；

1. 发件人/收件人的唯一值，不10208819391147662
2. 发件人/收件人之间的最后一条消息，NOT 10208819391147662 AND 发件人/收件人，即10208819391147662。
3. 第二条消息(消息)的时间戳。

为了实现以下目的，我有这个sql；

SELECT s1.*, max(c2.message), max(c2.timestamp)
FROM (
    SELECT sender as username 
    FROM messages
    WHERE sender <> '10208819391147662'
    UNION
    SELECT recipient as username 
    FROM messages
    WHERE recipient <> '10208819391147662'
) s1
LEFT JOIN messages c2 ON (s1.username = c2.sender OR s1.username = c2.recipient)
GROUP BY s1.username

我已使用 UNION 成功提取了第一列。但是使用 MAX 和 Group by 子句在第二个和第三个上没有运气。

我最终的预期结果应该是；

username, message, timestamp

"10204456572654160","And I reply like this","1459611758570"
"10153775515332771","Yes.. What's upp..","1459611900380"
"10204464403169266","How're you doin?","1459612000666"

这是到目前为止我的SQLFiddle。如有任何建议，我们将不胜感激。

Answer 1

实际上，最后这是我最终的 SQL，它实现了我想要的。

SELECT c2.id, s1.username, c2.message, max(c2.timestamp)
FROM (
    SELECT sender as username 
    FROM messages
    WHERE sender <> '10208819391147662'
    UNION
    SELECT recipient as username 
    FROM messages
    WHERE recipient <> '10208819391147662'
) s1
LEFT JOIN messages c2 ON (s1.username = c2.sender OR s1.username = c2.recipient)
GROUP BY s1.username
ORDER BY timestamp DESC

但不知何故，在 SQLFiddle ，这在 MYSQL 中无法按预期工作，但在 SQLITE 中却工作良好。