这是我的疑问:
SELECT msgId as `ID`,msgFromUserId , msgToUserId ,
DATE_FORMAT( msgDate,'%d/%m/%y %H:%i') as `time` , MID(msgText,1,30) as `text` ,
(CASE WHEN (msgFromUserId=292646) then b.user_login else a.user_login END) as `sender`
FROM tbl_messages inner join wp_users as a on tbl_messages.msgFromUserId=a.ID
inner join wp_users as b on tbl_messages.msgToUserId=b.ID
inner join tbl_forum_users u1 on tbl_messages.msgFromUserId=u1.user_ID
inner join tbl_forum_users u2 on tbl_messages.msgToUserId=u2.user_ID
where (msgFromUserId=292646 or msgToUserId=292646)
and tbl_messages.msgId in (SELECT max(msgId) FROM tbl_messages GROUP BY msgFromUserId, msgToUserId )
order by msgId desc我明白了:

我不想要复制行。他们之间的最后一句对话
发布于 2017-03-05 12:30:12
快速修复可以更改您的子查询。
SELECT max(msgId) FROM tbl_messages GROUP BY msgFromUserId, msgToUserId至
SELECT max(msgId)
FROM tbl_messages
GROUP BY LEAST(msgFromUserId, msgToUserId), GREATEST(msgFromUserId, msgToUserId)这将把292646到1和1到292646的消息组合在一起。
完整查询:
SELECT msgId as `ID`,msgFromUserId , msgToUserId ,
DATE_FORMAT( msgDate,'%d/%m/%y %H:%i') as `time` , MID(msgText,1,30) as `text` ,
(CASE WHEN (msgFromUserId=292646) then b.user_login else a.user_login END) as `sender`
FROM tbl_messages inner join wp_users as a on tbl_messages.msgFromUserId=a.ID
inner join wp_users as b on tbl_messages.msgToUserId=b.ID
inner join tbl_forum_users u1 on tbl_messages.msgFromUserId=u1.user_ID
inner join tbl_forum_users u2 on tbl_messages.msgToUserId=u2.user_ID
where (msgFromUserId=292646 or msgToUserId=292646)
and tbl_messages.msgId in (
SELECT max(msgId)
FROM tbl_messages
GROUP BY LEAST(msgFromUserId, msgToUserId), GREATEST(msgFromUserId, msgToUserId)
)
order by msgId desc为了提高性能,还应该将用户id条件移到子查询中:
SELECT max(msgId)
FROM tbl_messages
where (msgFromUserId=292646 or msgToUserId=292646) -- <-- here
GROUP BY LEAST(msgFromUserId, msgToUserId), GREATEST(msgFromUserId, msgToUserId)要以最好的方式使用索引,您应该使用UNION优化。但这看起来会很复杂:
SELECT max(msgId)
FROM (
SELECT msgToUserId as otherUserId, max(msgId) as msgId
FROM tbl_messages
WHERE msgFromUserId=292646
GROUP BY msgToUserId
UNION ALL
SELECT msgFromUserId as otherUserId, max(msgId) as msgId
FROM tbl_messages
WHERE msgToUserId=292646
GROUP BY msgFromUserId
) sub
GROUP BY otherUserId注意,这只是WHERE子句(tbl_messages.msgId in (...))中使用的子查询。
此子查询也可用作派生表,因此我们可以使用tbl_messages连接它。
SELECT msgId as `ID`,
msgFromUserId,
msgToUserId,
DATE_FORMAT( msgDate,'%d/%m/%y %H:%i') as `time`,
MID(msgText,1,30) as `text` ,
(CASE WHEN (msgFromUserId=292646) then b.user_login else a.user_login END) as `sender`
FROM (
SELECT max(msgId) as msgId
FROM (
SELECT msgToUserId as otherUserId, max(msgId) as msgId
FROM tbl_messages
WHERE msgFromUserId=292646
GROUP BY msgToUserId
UNION ALL
SELECT msgFromUserId as otherUserId, max(msgId) as msgId
FROM tbl_messages
WHERE msgToUserId=292646
GROUP BY msgFromUserId
) sub
GROUP BY otherUserId
) sub
inner join tbl_messages on tbl_messages.msgId = sub.msgId
inner join wp_users as a on tbl_messages.msgFromUserId=a.ID
inner join wp_users as b on tbl_messages.msgToUserId=b.ID
inner join tbl_forum_users u1 on tbl_messages.msgFromUserId=u1.user_ID
inner join tbl_forum_users u2 on tbl_messages.msgToUserId=u2.user_ID
order by tbl_messages.msgId desc您需要以下索引来支持子查询:
tbl_messages(msgFromUserId, msgToUserId [, msgId])
tbl_messages(msgToUserId, msgFromUserId [, msgId])https://stackoverflow.com/questions/42608275
复制相似问题