带有IN子句的Mysql查询，每项都有限制[重复]

This question already has an answer here:

这个问题已经有了答案:

Using LIMIT within GROUP BY to get N results per group? 13 answers
在组内使用极限，每组得到N个结果?13个答案

I have a table in MySQL with a field "class_id". I need to write a query which returns top 15 rows sorted using descending time order, for each value in the list with IN clause.

我在MySQL中有一个表，其中有一个字段“class_id”。我需要编写一个查询，该查询返回前15行，使用降序时间顺序对列表中的每个值使用in子句进行排序。

Query for explanation:

查询说明:

select * from table_x where class_id IN (1,2,3) sort by time_x desc limit 15;

In the above example query, I need to fetch top 15 rows for each class_id (1,2 and 3) sorted based on descending time order.

在上面的示例查询中，我需要根据降序时间顺序为每个class_id(1、2和3)获取前15行。

1 个解决方案

#1

You need the help of MySQL user defined variables

您需要MySQL用户定义的变量的帮助。

SELECT 
*
FROM 
(
    SELECT
        X.*,
        IF(@sameClass = class_id, @rn := @rn + 1,
             IF(@sameClass := class_id, @rn := 1, @rn := 1)
        ) AS rank
    FROM    table_x AS X
    CROSS JOIN (SELECT @sameClass := 0, @rn := 1 ) AS var
    WHERE   class_id IN (1, 2, 3) 
    ORDER BY class_id, time_x DESC
) AS t
WHERE t.rank <= 15
ORDER BY t.class_id, t.rank

In your case LIMIT 15 actually restricts the result set to contain at most 15 records which is not what you wanted.

在您的情况下，LIMIT 15实际上限制结果集最多包含15条记录，这不是您想要的。

#1