按用户分组并显示最新的MYSQL不起作用 [英] Group by user and show latest in MYSQL not working

问题描述

我有一个我正在编码的社交网络，但它与其他网络有点不同，在这种情况下，每个用户在Feed部分中只会有一次状态显示。

所以我需要排序的状态与最新的顶部的日期状态，但也由用户ID
组合，不幸的是我似乎无法得到这个工作....

这是我当前的查询：

pre $ SELECT
FROM status
WHERE userID！ =83＃这只是让我的状态不显示
GROUP BY userID
ORDER BY addedDate DESC
LIMIT 10

我希望看到最新的状态结果，并且每个用户只有一个，而我会看到第一个状态，因此group by正在工作，但不是按顺序。

在此先感谢。

解决方案

正如在罗宾的回答，这种方法是不可靠的，因为MySQL不能保证它将始终返回每个组的最新状态。你必须加入一个子查询来选择最新的状态（基于 addedDate ）。

p> SELECT *
从状态
自然科学JOIN（
选择用户ID，MAX（addedDate）作为addedDate
从状态
GROUP BY用户ID
）AS最新
ORDER BY addedDate DESC
极限10


注如果用户使用相同的 addedDate 进行多个状态更新，则服务器将返回所有这些更新（而Robin的查询将返回一个不确定的查询）;如果您需要控制这种情况，您需要定义如何确定应选择哪种状态更新。

I have a social network I am coding but it's a bit different than others, in this case there is only ever one status show per user on a feed section.

So I need to sort the status by date with the latest ones on top but also group by the userID unfortunately I can't seem to get this working....

This is my current query:

SELECT *
FROM status
WHERE userID != "83" #This is just so my statuses don't show
GROUP BY userID
ORDER BY addedDate DESC
LIMIT 10

I expect to see the latest status results and only one per user instead I see the first statuses so the group by is working but not the order by.

Thanks in advance.

解决方案

As mentioned in the comments to Robin's answer, that approach is unreliable because MySQL does not guarantee that it will always return the most recent status from each group. You must instead join your table with a subquery that selects the most recent status (based on addedDate).

SELECT   *
FROM     status
  NATURAL JOIN (
    SELECT   userID, MAX(addedDate) as addedDate
    FROM     status
    GROUP BY userID
  ) AS mostRecent
ORDER BY addedDate DESC
LIMIT    10

Note that if a user has multiple status updates with the same addedDate, the server will return all of them (whereas Robin's query would return an indeterminate one); if you need control over such a situation, you will need to define how one determines which such status update should be selected.

这篇关于按用户分组并显示最新的MYSQL不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！