MySQL:使用Desc命令发布多个分组 [英] MySQL: Multiple Group By With Desc Order Issue

问题描述

我已经访问过 MySQL by by之前的分组依据，用于单个分组依据"工作正常.我有2个按列分组的问题.

I already visited MySQL order by before group by for Single Group By it's working fine. I have issue with 2 group by columns.

我下面的表名为消息".消息是用户帖子的评论

I have below table named "messages". Messages are comments of User's Posts

id | posts_id | from_user_id | to_user_id | message_description | created_at
-----------------------------------------------------------------------------
1      1           1              2             test1             2016-09-06 10:00:00
2      1           1              2             test2             2016-09-06 11:00:00
3      1           4              2             test3             2016-09-06 09:00:00
4      1           4              2             test4             2016-09-06 15:00:00
5      2           1              2             test1             2016-09-06 10:00:00
6      2           1              2             test2             2016-09-06 11:00:00
7      2           4              2             test3             2016-09-06 09:00:00
8      2           4              2             test4             2016-09-06 15:00:00

查询结果输出应为

id | posts_id | from_user_id | to_user_id | message_description | created_at
-----------------------------------------------------------------------------
2      1           1              2             test2             2016-09-06 11:00:00
4      1           4              2             test4             2016-09-06 15:00:00
6      2           1              2             test2             2016-09-06 11:00:00
8      2           4              2             test4             2016-09-06 15:00:00

我要检索的内容是明智的&用户明智地使用他们的最新消息.

What i'm trying to retrieve post wise & user wise their latest messages.

我在下面进行了尝试，但是未按正确的顺序给出结果.

I gave tried below, but it's not giving result in correct order.

SELECT *
FROM messages
WHERE to_user_id = '2'
GROUP BY posts_id DESC,
         from_user_id
ORDER BY id DESC

推荐答案

此问题与SO上发布的其他数千个问题相同.手册中讨论的最佳解决方案是未关联的子查询，例如:

This problem is identical to thousands of others posted on SO. An optimal solution, as discussed in the manual, is an uncorellated subquery, e.g.:

SELECT x.* 
  FROM my_table x 
  JOIN 
     ( SELECT MAX(id) id 
         FROM my_table 
        WHERE to_user_id = 2 -- OPTIONAL
        GROUP 
           BY from_user_id
     ) y 
    ON y.id = x.id;

这篇关于MySQL:使用Desc命令发布多个分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！