设置
我有一个有两个表的数据库。其中一个用于收件箱消息(inbox
),另一个用于发件箱消息(outbox
)。它们具有相同的结构和一些记录。
+------------------------------------------------------------+
| inbox |
+------------------------------------------------------------+
| messageID | from | to | messageText | timestamp |
+------------------------------------------------------------+
| 1 | userA | userB | sometext | 2013-06-19 10:30 |
| 2 | userB | userC | sometext | 2013-06-19 10:40 |
| 3 | userC | userA | sometext | 2013-06-19 10:50 |
+------------------------------------------------------------+
和
+------------------------------------------------------------+
| outbox |
+------------------------------------------------------------+
| messageID | from | to | messageText | timestamp |
+------------------------------------------------------------+
| 1 | userA | userC | sometext | 2013-06-19 10:20 |
| 2 | userC | userB | sometext | 2013-06-19 10:30 |
| 3 | userB | userA | sometext | 2013-06-19 10:35 |
+------------------------------------------------------------+
我需要从消息所在的任何表(收件箱或发件箱)中选择来自特定用户的每个对话的每条最后消息(在本例中,我想检索来自用户对话的每条最后消息)。我已经通过以下查询从收件箱和发件箱中为每个对话选择了最后一条消息:
SELECT * FROM
(
SELECT from, to, timestamp, messageText, messageID
FROM inbox
WHERE to = 'userA'
ORDER BY timestamp DESC
)
AS tmp_table GROUP BY from
UNION
SELECT * FROM
(
SELECT from, to, timestamp, messageText, messageID
FROM outbox
WHERE from = 'userA'
ORDER BY timestamp DESC
)
AS tmp_table GROUP BY to
ORDER BY timestamp DESC
所以现在我需要检查哪个消息是更新的—收件箱中的消息还是发件箱中的消息(当然,如果两个用户之间的两个表中都存在消息),并且只返回最新的消息。
或者可能我的方法是彻头彻尾的愚蠢-请评论:D谢谢。
从每个表中获取前1条记录,然后将结果合并:
SELECT 'outbox', *
FROM outbox
WHERE `from` = 'userA'
ORDER BY timestamp DESC
LIMIT 1
UNION ALL
SELECT 'inbox', *
FROM inbox
WHERE `from` = 'userA'
ORDER BY timestamp DESC
LIMIT 1
指出:
1)注意from
周围的反引号。FROM
在几乎所有sql数据库中都是保留字,因此您的查询永远不会工作而不会出现语法错误
2)注意两个子查询中的硬编码'inbox'
和'outbox'
字符串-它们在那里告诉您找到的记录来自哪个表。
根据在表上设置的索引,可能会编写一个执行速度更快的查询,但这应该可以工作:
select
sq.main_user,
sq.other_user,
coalesce(i.messageText, o.messageText) messageText
from
(select
main_user, other_user, max(msg_time) last_msg
from
(select
`from` main_user, `to` other_user, `timestamp` msg_time
from outbox
where `from` = 'userA'
union all
select
`to` main_user, `from` other_user, `timestamp` msg_time
from inbox
where `to` = 'userA') uq
group by main_user, other_user) sq
left join
outbox o on sq.main_user = o.`from` and sq.last_msg = o.`timestamp`
left join
inbox i on sq.main_user = i.`to` and sq.last_msg = i.`timestamp`
(如果您希望查看所有用户的对话,而不仅仅是userA,则可以从最内层子查询中省略where
子句)