mysql选择带限制的内部连接 [英] mysql select inner join with limit

问题描述

我有2个表格，类别和文章，我希望每个类别获得5篇文章，表格如下所示:

I have 2 tables, category and articles, I want to get 5 articles for each category, the tables look like this:

category: id, name
articles: id, title, body, category_id

所以我通常做的是INNER JOIN，但是我得到了所有行，如何指定每个类别仅需要5行，我想它在SELECT内需要一个SELECT吗?

So what I normally do is INNER JOIN, but I get all rows, how can I specify that I need only 5 rows per category, I imagine it would need a SELECT within a SELECT ?

推荐答案

您可以使用等级查询mysql没有针对此类结果的窗口函数，以便每组获取n条记录，因此我不建议使用group_concat解决方案因为正如articles条款所述，如果您增加此限制，则可以有足够的数据并轻松通过1024个字符的限制约束，因此它也依赖于

You can use a rank query mysql does not have window functions for this type of results to get n records per group,i will not suggest a group_concat solution because as articles terms says that there can be enough data and easily by pass the 1024 character limit constraint if you increase this limit it also has a dependency on max_allowed_packet too

SELECT * FROM (
SELECT *,
@r:= CASE WHEN @g = c.id THEN @r +1 ELSE 1 END rownum,
@g:= c.id catgroup
 FROM category c
 JOIN articles a ON (c.id = a,category_id)
CROSS JOIN (SELECT @g:=0,@r:=0) t2
ORDER BY c.id , a.`date` desc
) t
 WHERE rownum <= 5

上面将每篇文章排在其类别组内，您可以看到rownum别名的结果，而在外部查询中，只需将文章的结果过滤为每个类别组5篇

Above will rank each article within in its category group you can see the result of rownum alias and in outer query just filter the results of articles to 5 per category group

这篇关于mysql选择带限制的内部连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！