MySQL GROUP BY 表现奇怪，没有返回预期结果

Question

我正在尝试返回 ID 258776 的行，这是我迄今为止尝试过的操作...

这是我的示例，我使用 AND msg.uid 仅获取一条消息。我想返回每个线程的最新帖子，因此在 1 个线程只有 2 条消息的示例中，我希望返回 ID 258776 的行。

没有 GROUP BY:

SELECT main.id, main.message_id, main.inbox_id, main.uid, main.body, main.created_at FROM (
                SELECT i.id as inbox_id, i.message_id, msg.* FROM inbox AS i
                INNER JOIN message AS msg ON i.message_id = msg.id
                WHERE i.profile_id = 2135
                AND i.is_sent = 0
                AND i.is_deleted = 0
                #AND msg.uid = '570cc3a568402'
                ORDER BY msg.updated_at DESC
            ) AS main #GROUP BY main.uid


= ID =  =MSG =  =INB =  = UID       =   = BD =   = CREATED         =  
258776  258776  524785  570cc3a568402   wtf      2016-06-22 11:34:29  
217149  217149  438907  570cc3a568402   <br />   2016-04-12 11:45:09  

尝试使用 GROUP BY

SELECT main.id, main.message_id, main.inbox_id, main.uid, main.body, main.created_at FROM (
                SELECT i.id as inbox_id, i.message_id, msg.* FROM inbox AS i
                INNER JOIN message AS msg ON i.message_id = msg.id
                WHERE i.profile_id = 2135
                AND i.is_sent = 0
                AND i.is_deleted = 0
                #AND msg.uid = '570cc3a568402'
                ORDER BY msg.updated_at DESC
            ) AS main GROUP BY main.uid

= ID =  =MSG =  =INB =  = UID       =   = BD =   = CREATED         =  
217149  217149  438907  570cc3a568402   <br />   2016-04-12 11:45:09  

切换到 ASC 但 GROUP BY 给出相同的结果？

SELECT main.id, main.message_id, main.inbox_id, main.uid, main.body, main.created_at FROM (
                SELECT i.id as inbox_id, i.message_id, msg.* FROM inbox AS i
                INNER JOIN message AS msg ON i.message_id = msg.id
                WHERE i.profile_id = 2135
                AND i.is_sent = 0
                AND i.is_deleted = 0
                #AND msg.uid = '570cc3a568402'
                ORDER BY msg.updated_at ASC
            ) AS main GROUP BY main.uid

= ID =  =MSG =  =INB =  = UID       =   = BD =   = CREATED         =  
217149  217149  438907  570cc3a568402   <br />   2016-04-12 11:45:09

我认为如果我不需要使用 INNER join ，它应该可以正常工作？ :(

编辑:

以下是来自 message 表和 inbox 表的更多数据。

消息

= ID =  =FROMID=  = UID       = = TITLE = = BODY =  = CREATED         =
258776  52169     570cc3a568402 RE: RE:   wtf       2016-06-22 11:34:29  
258775  2135      570cc3a568402 RE: You   Testtest  2016-06-22 11:31:29 
258774  34833     576a590fdf9e5 RE: Sure  < 3 < 3   2016-06-22 11:24:08  
258773  34833     576a590fdf9e5 RE: Sure  sok       2016-06-22 11:23:57     
258772  34833     576a590fdf9e5 RE: Sure  hey hey   2016-06-22 11:23:46     

收件箱

= ID =  = PROFILE =   = MSG_ID =   = IS_SENT = = IS_READ = = IS_DELETED =
524785  2135          258776       0           0           0
524784  52169         258776       1           1           0
524783  52169         258775       0           1           0
524782  2135          258775       1           0           0
524781  2135          258774       0           1           0

Answer 1

如果您想要符合您条件的最大 ID，您应该使用

SELECT main.id, main.message_id, main.inbox_id, main.uid, main.body, main.created_at 
    FROM (
            SELECT i.id as inbox_id, i.message_id, msg.* FROM inbox AS i
            INNER JOIN message AS msg ON i.message_id = msg.id
            WHERE i.id = (    

                SELECT max(i.id)  FROM inbox AS i
                INNER JOIN message AS msg ON i.message_id = msg.id
                WHERE i.profile_id = 2135
                AND i.is_sent = 0
                AND i.is_deleted = 0
                AND msg.uid = '570cc3a568402'

            )


        ) AS main

像评论中所说的分组是针对聚合函数(带有multiplr结果)，你没有这个，那么你就不需要