mysql - SQLITE查询以不同条件联接两个相同的表

我有一个名为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"

现在，我想将值传递给函数e.g. 10208819391147662并显示结果，如以下各列所示；

sender/recipient的唯一值，不是10208819391147662
message不是sender/recipient和10208819391147662是sender/recipient之间的最后一个10208819391147662。
第二个timestamp（消息）。

为了达到以下目的，我有这个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

我已经成功地使用1st column提取了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。任何建议表示赞赏。

最佳答案

实际上，最后这是我完成我想要的最终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中却很好。

关于mysql - SQLITE查询以不同条件联接两个相同的表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/36375931/