Possible Duplicate:
mysql: Using LIMIT within GROUP BY to get N results per group?可能重复:mysql:在GROUP BY中使用LIMIT来获得每组N个结果?
I'm trying to write a query that will function like a LIMIT statement, but it will limit the results returned per distinct column value instead of the entire result set. I don't know the terminology to explain what I want precisely, so I'll use an example.
我正在尝试编写一个类似于LIMIT语句的查询,但它会限制每个不同列值而不是整个结果集返回的结果。我不知道要解释我想要什么的术语,所以我将使用一个例子。
In this example (which is a much simplified version of what I'm actually trying to do) I'm trying to SELECT the 5 most recent messages for each user in the system. The below table maps userIDs to messageIDs. Message IDs are assigned chronologically so a more recent message will have a higher ID value.
在这个例子中(这是我正在尝试做的简化版本)我正在尝试为系统中的每个用户选择5条最新消息。下表将userID映射到messageID。消息ID按时间顺序分配,因此更新的消息将具有更高的ID值。
TABLE Messages (
userId int,
messageId int
)
1 个解决方案
#1
2
Perhaps this is what you're looking for:
也许这就是你要找的东西:
SELECT userId, messageId FROM (
SELECT userId, messageId,
if(@uid = userId, @cnt := @cnt + 1, (@cnt := 0 || @uid := userId)) cnt
FROM Messages
ORDER BY userId, messageId DESC
) msg_list
WHERE cnt <= 5;
#1
2
Perhaps this is what you're looking for:
也许这就是你要找的东西:
SELECT userId, messageId FROM (
SELECT userId, messageId,
if(@uid = userId, @cnt := @cnt + 1, (@cnt := 0 || @uid := userId)) cnt
FROM Messages
ORDER BY userId, messageId DESC
) msg_list
WHERE cnt <= 5;