下面的代码向我展示了轮播上按日期排序的帖子，某些类别有例外。
但如果有人在同一个类别的职位，它采取了完整的名单。我正在尝试做的是限制在两个职位每个类别。我会很感激任何建议，使工作。
当前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表格：员额
+--------+------------+-------------+---------------+---------------------+------------+
| 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 |
+--------+------------+-------------+---------------+---------------------+------------+表：类别
+------------+------------------+-----------------+
| 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 |
+--------+------------+-------------+---------------+---------------------+------------+
1条答案
按热度按时间yizd12fk1#
这是使用子查询获取输出的一种方法。您可以首先将每个类别的行限制为两行，然后获取帖子。
我认为在预期的输出中，您应该获得idCategory = 4的帖子，因为该类别只有一个帖子。