首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >Mysql -为每个用户选择最后一行

问Mysql -为每个用户选择最后一行
EN

Stack Overflow用户
提问于 2017-03-05 12:19:55
回答 1查看 462关注 0票数 0

这是我的疑问:

代码语言:javascript
复制
 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

我明白了:

​

​

我不想要复制行。他们之间的最后一句对话

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2017-03-05 12:30:12

快速修复可以更改您的子查询。

代码语言:javascript
复制
SELECT max(msgId) FROM tbl_messages   GROUP BY msgFromUserId, msgToUserId

至

代码语言:javascript
复制
SELECT max(msgId) 
FROM tbl_messages
GROUP BY LEAST(msgFromUserId, msgToUserId), GREATEST(msgFromUserId, msgToUserId)

这将把292646到1和1到292646的消息组合在一起。

完整查询:

代码语言:javascript
复制
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条件移到子查询中:

代码语言:javascript
复制
SELECT max(msgId) 
FROM tbl_messages
where (msgFromUserId=292646 or msgToUserId=292646) -- <-- here
GROUP BY LEAST(msgFromUserId, msgToUserId), GREATEST(msgFromUserId, msgToUserId)

要以最好的方式使用索引,您应该使用UNION优化。但这看起来会很复杂:

代码语言:javascript
复制
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连接它。

代码语言:javascript
复制
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

您需要以下索引来支持子查询:

代码语言:javascript
复制
tbl_messages(msgFromUserId, msgToUserId [, msgId])
tbl_messages(msgToUserId, msgFromUserId [, msgId])
票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/42608275

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档