每列值的SQL限制结果 [英] SQL Limit results per column value

问题描述

可能重复:
mysql:在GROUP BY中使用LIMIT每组获得N个结果?

Possible Duplicate:
mysql: Using LIMIT within GROUP BY to get N results per group?

我正在尝试编写一个查询，该查询的功能类似于LIMIT语句，但是它将限制每个不同的列值而不是整个结果集返回的结果.我不知道用什么术语来准确解释我想要的东西，所以我将举一个例子.

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.

在此示例中(这是我实际上想做的简化版)，我试图为系统中的每个用户SELECT 5条最新消息.下表将用户ID映射到messageID.邮件ID是按时间顺序分配的，因此，更新的邮件将具有较高的ID值.

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.

TABLE Messages (
 userId int,
 messageId int
)

推荐答案

也许这就是您想要的:

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;

这篇关于每列值的SQL限制结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！