如何在查询中按类别限制结果

Question

下面的代码显示了carousel上按日期排序的帖子，以及某些类别的例外。

但是如果有人在同一类别中发布，它将获取完整的列表。我想做的是限制在两个帖子每个类别。如果有什么建议，我将不胜感激。

当前SQL:

SELECT 
p.idPost,
p.isAvailable,
p.title,
p.description,
p.insertDate,
c.idCategoryParent
FROM 
posts p
INNER JOIN categories c
ON c.idCategory = p.idCategory
WHERE 
(
p.isAvailable = 1
AND (c.idCategoryParent NOT IN (3)) 
)
ORDER BY 
p.insertDate DESC 
LIMIT 12

table: post

+--------+------------+-------------+---------------+---------------------+------------+
| idPost | idCategory | isAvailable |    title      |    description      | insertDate |
+--------+------------+-------------+---------------+---------------------+------------+
|   1    |      6     |     1       |   Red Ford    |  The Ford red car...| 2022-12-15 |
|   2    |      4     |     1       |   Bike cross  |  The bike cross...  | 2022-12-15 |
|   3    |      5     |     1       |   Black Honda |  The Black car...   | 2022-12-15 |
|   4    |      6     |     1       |   Blue Ford   |  The blue Ford      | 2022-12-15 |
|   5    |      6     |     1       |   Pink Ford   |  The pink Ford      | 2022-12-15 |
|   6    |      6     |     1       |   Green Ford  |  The Green Ford car | 2022-12-15 |
|   7    |      9     |     1       |   Airbus I    |  The Airbus jet I   | 2022-12-15 |
|   8    |      9     |     1       |   Aibus II    |  The Airbus jet II  | 2022-12-15 |
|   9    |      9     |     1       |   Airbus III  |  The Airbus jet III | 2022-12-15 |
+--------+------------+-------------+---------------+---------------------+------------+

table: categories

+------------+------------------+-----------------+
| idCategory | idCategoryParent |      name       |
+------------+------------------+-----------------+
|     1      |        0         |       Car       |
|     2      |        0         |       Plane     |
|     3      |        0         |       Bike      |
|     4      |        3         |    Bike Cross   |
|     5      |        1         |       Honda     |
|     6      |        1         |       Ford      |
|     7      |        1         |       Mercedes  |
|     8      |        2         |       Boeing    |
|     9      |        2         |       Airbus    |    
+------------+------------------+-----------------+

当前结果:

+--------+------------+-------------+---------------+---------------------+------------+
| idPost | idCategory | isAvailable |    title      |    description      | insertDate |
+--------+------------+-------------+---------------+---------------------+------------+
|   1    |      6     |     1       |   Red Ford    |  The Ford red car...| 2022-12-15 |
|   3    |      5     |     1       |   Black Honda |  The Black car...   | 2022-12-15 |
|   4    |      6     |     1       |   Blue Ford   |  The blue Ford      | 2022-12-15 |
|   5    |      6     |     1       |   Pink Ford   |  The pink Ford      | 2022-12-15 |
|   6    |      6     |     1       |   Green Ford  |  The Green Ford car | 2022-12-15 |
|   7    |      9     |     1       |   Airbus I    |  The Airbus jet I   | 2022-12-15 |
|   8    |      9     |     1       |   Aibus II    |  The Airbus jet II  | 2022-12-15 |
|   9    |      9     |     1       |   Airbus III  |  The Airbus jet III | 2022-12-15 |
+--------+------------+-------------+---------------+---------------------+------------+

每个类别最多2个职位的预期结果:

+--------+------------+-------------+---------------+---------------------+------------+
| idPost | idCategory | isAvailable |    title      |    description      | insertDate |
+--------+------------+-------------+---------------+---------------------+------------+
|   1    |      6     |     1       |   Red Ford    |  The Ford red car...| 2022-12-15 |
|   3    |      5     |     1       |   Black Honda |  The Black car...   | 2022-12-15 |
|   4    |      6     |     1       |   Blue Ford   |  The blue Ford      | 2022-12-15 |
|   7    |      9     |     1       |   Airbus I    |  The Airbus jet I   | 2022-12-15 |
|   8    |      9     |     1       |   Aibus II    |  The Airbus jet II  | 2022-12-15 |
+--------+------------+-------------+---------------+---------------------+------------+

Answer 1

这是使用子查询获取输出的一种方法。您可以首先将每个类别的行数限制为两行，然后获取帖子。

SELECT p.* FROM post p INNER JOIN categories c ON p.idCategory = c.idCategory 
WHERE p.isAvailable = 1 AND p.idPost IN (SELECT idPost FROM post 
WHERE idCategory = p.idCategory LIMIT 2) ORDER BY p.insertDate

我认为在预期的输出中，您应该获得idCategory = 4的帖子，因为您只有一个该类别的帖子。