我在MySQL中有一张桌子,里面有对话。这些会话由消息组成。单个对话如下表所示。
Importance和eId只是偶尔设置的,我想从表中获取的是对话中的最后一条消息(messageId = 4),但是最后一条消息是importance,最后一条消息是eId。
所以,从这张表中
+----------------+-----------+----------+----------+------------+-------+---------+
| conversationId | messageId | time | status | importance | eId | message |
+----------------+-----------+----------+----------+------------+-------+---------+
| 25 | 4 | 11:00:00 | feedback | NULL | NULL | d.. |
+----------------+-----------+----------+----------+------------+-------+---------+
| 25 | 3 | 10:00:00 | open | MEDIUM | NULL | c.. |
+----------------+-----------+----------+----------+------------+-------+---------+
| 25 | 2 | 09:00:00 | feedback | NULL | 123 | b... |
+----------------+-----------+----------+----------+------------+-------+---------+
| 25 | 1 | 08:00:00 | open | HIGH | NULL | a... |
+----------------+-----------+----------+----------+------------+-------+---------+我需要得到这个结果
+----------------+-----------+----------+----------+------------+-------+---------+
| conversationId | messageId | time | status | importance | eId | message |
+----------------+-----------+----------+----------+------------+-------+---------+
| 25 | 4 | 11:00:00 | feedback | MEDIUM | 123 | d.. |
+----------------+-----------+----------+----------+------------+-------+---------+我无法执行查询。任何帮助都会得到重视。谢谢。
发布于 2013-11-20 01:12:07
这不一定是最好的解决方案,因为我不得不在我本地的SQL Server中创建它(SQL fiddle在尝试时关闭了,我没有安装MySQL ),还有一个“快速而肮脏”的查询(意思是:可能有更好的解决方案来解决你的问题),但由于我渴望帮助我发布它。
如果您碰巧在等待来自社区的另一个可能更好的解决方案,我不会感到生气:)这就是我想说的:)
SELECT TOP 1 conversationId,messageId,RecTime,ConvStatus,
(
SELECT TOP 1 importance
FROM Conversations
WHERE importance IS NOT NULL
ORDER BY messageId DESC
) AS importance,
(
SELECT TOP 1 eId
FROM Conversations
WHERE eId IS NOT NULL
ORDER BY messageId DESC
) AS eId,
UsrMessage
FROM Conversations
ORDER BY messageId DESC
GO
(不要忘记,您可能需要更改“格式化项目”才能在MySQL中工作)
发布于 2013-11-20 03:21:16
如果表中有多个conversationId,并且您希望同时获得所有conversationIds的所需结果,那么我认为您需要在子查询中联接3个子查询。类似于下面的3条:
SELECT messages.conversationId, messages.messageId, messages.time, messages.status, messages.message
FROM messages
JOIN (
SELECT conversationId, MAX(messageId) as messageId
FROM messages
GROUP BY conversationId) as m2
ON (messages.messageId = m2.messageId);
SELECT messages.conversationId, messages.importance
FROM messages
JOIN (
SELECT conversationId, MAX(messageId) as messageId
FROM messages
WHERE importance IS NOT NULL
GROUP BY conversationId) as m3
ON (messages.messageId = m3.messageId);
SELECT messages.conversationId, messages.eId
FROM messages
JOIN (
SELECT conversationId, MAX(messageId) as messageId
FROM messages
WHERE eId IS NOT NULL
GROUP BY conversationId) as m4
ON (messages.messageId = m4.messageId);连接将如下所示:
SELECT
main.conversationId,
main.messageId,
main.time,
main.status,
importance.importance,
eId.eId,
main.message
FROM (
SELECT messages.conversationId, messages.messageId, messages.time, messages.status, messages.message
FROM messages
JOIN (
SELECT conversationId, MAX(messageId) AS messageId
FROM messages
GROUP BY conversationId) AS m2
ON (messages.messageId = m2.messageId)
) AS main
JOIN (
SELECT messages.conversationId, messages.importance
FROM messages
JOIN (
SELECT conversationId, MAX(messageId) AS messageId
FROM messages
WHERE importance IS NOT NULL
GROUP BY conversationId) AS m3
ON (messages.messageId = m3.messageId)
) AS importance
JOIN (
SELECT messages.conversationId, messages.eId
FROM messages
JOIN (
SELECT conversationId, MAX(messageId) AS messageId
FROM messages
WHERE eId IS NOT NULL
GROUP BY conversationId) AS m4
ON (messages.messageId = m4.messageId)
) AS eId
ON (
main.conversationId = importance.conversationId
AND main.conversationId = eId.conversationId
);这里有一个sqlfiddle:http://sqlfiddle.com/#!2/857aa/38。这假设messageId是唯一的。如果它不是唯一的,那么你需要在messageId和conversationId上加入。
发布于 2013-11-20 08:39:11
可能有点老套,但应该能做到:
SELECT c.conversationId as convId, max(c.messageId), t.time, s.status, i.importance, e.eId, c.message
FROM convs c,
(SELECT conversationId, max(time) AS time FROM convs) t,
(SELECT conversationId, status FROM convs WHERE status IS NOT NULL ORDER BY messageId DESC) s,
(SELECT conversationId, importance FROM convs WHERE importance IS NOT NULL ORDER BY messageId DESC) i,
(SELECT conversationId, eId FROM convs WHERE eId IS NOT NULL ORDER BY messageId DESC) e
WHERE 1=1
AND t.conversationId = c.conversationId
AND s.conversationId = c.conversationId
AND i.conversationId = c.conversationId
AND e.conversationId = c.conversationId
GROUP BY c.conversationIdhttps://stackoverflow.com/questions/20076159
复制相似问题