Mysql查询限制项数

Question

我有两个表:collections 和 collection_items。

我试图弄清楚如何为每个集合进行选择，在同一查询中最多获取 4 个 collection_items。

某些集合可能包含超过 4 个项目，但我只需要最多显示 4 个。

以下查询返回每个集合的所有项目，但不知道如何将其限制为 4。

SELECT  cn.*, o.url_thumb
FROM collection_names as cn LEFT JOIN collection_items as ci ON ci.collection_id=cn.id
     LEFT JOIN objects as o ON o.ID=ci.object_id
WHERE cn.public=1
ORDER BY cn.fecha DESC
LIMIT 0, 20

表定义:

Answer 1

利用组行数和子查询的分配来尝试此解决方案。

select id, user_id, image_object_id, name, public, description, fecha, url_thumb
from
(
    select 
        *,
        IF(id = @last_id, @grp_rn := @grp_rn + 1, @grp_rn := 1) as grp_rn
    from 
    (
        SELECT  cn.*, o.url_thumb
        FROM collection_names as cn LEFT JOIN collection_items as ci ON ci.collection_id=cn.id
             LEFT JOIN objects as o ON o.ID=ci.object_id
        WHERE cn.public=1
        ORDER BY cn.fecha DESC
        LIMIT 0, 20
    ) as t cross join (select @grp_rn := 0, @last_id := NULL) param
    order by t.id
) as t2
where t2.grp_rn <=4;