MySQL:按ID排序分组结果 - MySQL: Order grouped results by ID

MySQL: Order grouped results by ID

本文关键字：结果排序 ID MySQL | 更新日期: 2023-09-27

当我放置分组时，我有一个小问题来按ID排序我的结果:

$get_seen_messages = mysql_query("SELECT * 
                                  FROM chat 
                                  WHERE userto='$login_cookie' 
                                    AND status='seen' 
                                  GROUP BY userfrom 
                                  ORDER BY id DESC");

哪个id ?您应该永远不要使用SELECT *和GROUP BY。当您使用GROUP BY时，所有未聚合的列都应该在GROUP BY中。因此，这是一个格式正确的查询

SELECT userfrom, MAX(id) as id
FROM chat
WHERE userto = '$login_cookie' AND status = 'seen'
GROUP BY userfrom
ORDER BY MAX(id) DESC;

我怀疑，虽然，你想从每个userfrom的聊天最近的行。在这种情况下，正确形成的查询看起来像:

select c.*
from chat c
where userto = '$login_cookie' and status = 'seen' and
      id = (select max(c2.id)
            from chat c2
            where c2.userto = c.userto and c2.status = c.status
           );

您尝试过子查询吗?

$get_seen_messages = mysql_query("
    SELECT * FROM
        (SELECT * 
            FROM chat 
            WHERE userto='$login_cookie' 
            AND status='seen' 
            GROUP BY userfrom) as subquery 
    ORDER BY id DESC");