php - MySql，限制每个类别的文章数量

Question

我正在尝试创建简单的新闻网站，我想在主页上显示所有类别，并将每个类别中的文章数量限制为 5 篇。这就是我目前所得到的。

表格类别

id    name    
========================================
1    World
2    Sports
3    Economy
4    Music

表格文章

id   category_id  name  
===============================
1    1            Article 1
2    1            Article 2
3    1            Article 3
4    2            Article 11
5    2            Article 22
6    3            Article 33
7    4            Article 111
8    3            Article 222
9    3            Article 333

和我的查询:

SELECT
  a.category_id,
  c.name as catname,
  a.name as artname
FROM
  categories AS c
  LEFT JOIN articles AS a
    ON a.category_id = c.id
LIMIT 5;

问题是限制应用于类别表，但我实际上需要应用于文章表。

Answer 1

如果你有一个带有窗口函数的较新的 MySQL 版本，你可以使用这样的查询:您只需指定要查看的字段即可

SELECT c.*,a.*
FROM categories c
LEFT JOIN (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY id DESC ) AS rnr
        FROM articles ) a 
    ON c.id = a.category_id
WHERE rnr < 4
ORDER BY c.id, rnr;

示例

MariaDB [bernd]> select * from articles;
+----+-------------+-------------+
| id | category_id | name        |
+----+-------------+-------------+
|  1 |           1 | Article 1   |
|  2 |           1 | Article 2   |
|  3 |           1 | Article 3   |
|  4 |           2 | Article 11  |
|  5 |           2 | Article 22  |
|  6 |           3 | Article 33  |
|  7 |           4 | Article 111 |
|  8 |           3 | Article 222 |
|  9 |           3 | Article 333 |
+----+-------------+-------------+
9 rows in set (0.00 sec)

MariaDB [bernd]> SELECT c.*,a.*
    -> FROM categories c
    -> LEFT JOIN (
    ->     SELECT
    ->         *,
    ->         ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY id DESC ) AS rnr
    ->         FROM articles ) a
    ->     ON c.id = a.category_id
    -> WHERE rnr < 4
    -> ORDER BY c.id, rnr;
+----+---------+------+-------------+-------------+------+
| id | name    | id   | category_id | name        | rnr  |
+----+---------+------+-------------+-------------+------+
|  1 | World   |    3 |           1 | Article 3   |    1 |
|  1 | World   |    2 |           1 | Article 2   |    2 |
|  1 | World   |    1 |           1 | Article 1   |    3 |
|  2 | Sports  |    5 |           2 | Article 22  |    1 |
|  2 | Sports  |    4 |           2 | Article 11  |    2 |
|  3 | Economy |    9 |           3 | Article 333 |    1 |
|  3 | Economy |    8 |           3 | Article 222 |    2 |
|  3 | Economy |    6 |           3 | Article 33  |    3 |
|  4 | Music   |    7 |           4 | Article 111 |    1 |
+----+---------+------+-------------+-------------+------+
9 rows in set (0.00 sec)

MariaDB [bernd]>