MySQL：根据列值限制接收的结果数量结合查询

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

我已经完成了对这个问题的研究,但我找不到解决方案。

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

我有以下查询,给我一个“some_id”的列表:

SELECT some_id FROM example GROUP BY some_id

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.

我有以下查询,它将获得“some_id”等于数字的行的5个最新条目的列表。

SELECT * FROM example
WHERE some_id = 1
ORDER BY last_modified DESC
LIMIT 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.

如何只使用一个查询,为每个“some_id”从表“example”中获取前5个最新条目?如果“some_id”的条目少于5个,则可以包含它们,如果这样可以减少复杂性。

Many thanks!

2 个解决方案

#1

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

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

How do I limit the number of rows per field value in SQL?

如何限制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

#2

-2

Hi Please use Group by and having clause for check limit for every id..

嗨请使用分组依据和每个id的检查限制条款。

SELECT * FROM `example`
GROUP BY some_id
HAVING count( * ) <= 5
ORDER BY last_modified DESC

It will check for every some_id and check if number of rows for some_id is <=5 then it will be display result.

它将检查每个some_id并检查some_id的行数是否<= 5然后它将是显示结果。

#1