我有两个表，一个存储传入消息，一个存储传出消息。我想要的是能够有一个消息的对话视图，这样来自/到同一用户id的所有传入和传出消息都被分组，并且对话是按最新消息（传入或传出）排序的
Outgoing
----------
user_id
time
message
Incoming
----------
user_id
time
message
我想显示的结果是
-> User A 9:10 pm Nice ...
<- User A 8:45 pm Our special is pepperoni!
-> User A 8:00 pm What's your special dish?
<- User B 9:00 pm We open at 5
-> User B 6:56 pm Hello What time to you open?
<- User C 8:43 pm Thanks!
-> User C 4:00 pm Loved the pizza today!!
你知道怎么写一个查询来做这个吗？
编辑
如果用户B然后回发短信，结果应该是：
-> User B 9:15 pm Ok great!
<- User B 9:00 pm We open at 5
-> User B 6:56 pm Hello What time to you open?
-> User A 9:10 pm Nice ...
<- User A 8:45 pm Our special is pepperoni!
-> User A 8:00 pm What's your special dish?
<- User C 8:43 pm Thanks!
-> User C 4:00 pm Loved the pizza today!!
最佳答案
您需要UNION这两个表并相应地排序（ORDER BY）：
SELECT
'<-' AS direction, user_id, time, message
FROM
Outgoing
UNION ALL
SELECT
'->', user_id, time, message
FROM
Incoming
ORDER BY
user_id ASC,
time DESC ;
在对复杂排序的其他解释之后：
SELECT
CASE WHEN m.d = 1 THEN '<-' ELSE '->' END AS direction,
m.user_id, m.time, m.message
FROM
( SELECT
u.user_id,
GREATEST( COALESCE(mo.time, mi.time),
COALESCE(mi.time, mo.time) ) AS maxtime
FROM
( SELECT user_id FROM Outgoing
UNION
SELECT user_id FROM Incoming
) AS u
LEFT JOIN
( SELECT user_id, MAX(time) AS time FROM Outgoing GROUP BY user_id
) AS mo
ON mo.user_id = u.user_id
LEFT JOIN
( SELECT user_id, MAX(time) AS time FROM Incoming GROUP BY user_id
) AS mi
ON mi.user_id = u.user_id
) AS b
JOIN
( SELECT 1 AS d, user_id, time, message FROM Outgoing
UNION ALL
SELECT 2 AS d, user_id, time, message FROM Incoming
) AS m
ON m.user_id = b.user_id
ORDER BY
b.maxtime ASC,
m.user_id ASC,
m.time DESC ;
关于mysql - SQL从两个表中获取对话，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13636063/