MySQL:基于列值限制接收到的结果数合并查询 [英] MySQL: Limiting number of results received based on a column value | Combining queries

问题描述

我已经对此问题进行了研究，但是找不到解决方法.

I've done research on this problem, but am having trouble finding a solution.

我有以下查询，为我提供了"some_id"的列表:

I have the following query that gives me a list of "some_id"s:

SELECT some_id FROM example GROUP BY some_id

我有以下查询，它将为"some_id"等于数字的行获取5个最新条目的列表.

And I have the following query that will get a list of the 5 most recent entries for a row that has "some_id" equal to a number.

SELECT * FROM example
WHERE some_id = 1
ORDER BY last_modified DESC
LIMIT 5

如何仅使用一个查询从表"example"中获取每个"some_id"的前5个最新条目?如果"some_id"的条目少于5个，则可以将它们包括在内，这样会使事情变得不那么复杂.

How can I get the the top 5 most recent entries from the table "example" for each "some_id", using only one query? If there are less than 5 entries for a "some_id", it is okay to include them, if that makes things less complex.

非常感谢！

推荐答案

在查看以下帖子中的第一个答案时找到了答案:

Found the answer when looking at the first answer in the following post:

如何进行限制SQL中每个字段值的行数?

我已对其进行修改以满足我的特定需求:

I've modified it to fit my specific needs:

SELECT * FROM
(
    SELECT *, @num := if(@some_id = some_id, @num := @num + 1, 1) as row_num,
           @some_id := some_id as some_id
    FROM example
    ORDER BY last_modified DESC
) as e
WHERE row_num <= 5

这篇关于MySQL:基于列值限制接收到的结果数合并查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！