mysql - 特定的 MySql 查询

Question

有如下表格

Post(*id, name, description, cat, publish_date)
Category(*id, name)

是否有可能在一个查询中获取(最大)每个不同类别的前 N ​​个元素？

假设 N=3，我需要以下结果:

结果集:

["1", "Name1","Descr","cat1"]
["2", "Name1","Descr","cat1"]
["3", "Name1","Descr","cat1"]
["10","Name1","Descr","cat2"]
["20","Name1","Descr","cat2"]
["22","Name1","Descr","cat2"]
["25","Name1","Descr","cat3"]
["30","Name1","Descr","cat3"]
["19","Name1","Descr","cat3"]

等等。

我需要这个，通过一个查询获取每个类别的前 N ​​篇文章(因此不询问特定类别，而是询问表中的所有类别)

这可能吗？如果是，正确的查询是什么？

Answer 1

此查询将执行您需要的操作。如果任何类别的帖子少于 3 个，它仍然有效。

SELECT P.id,P.name,P.description,C.name 
FROM Post P
LEFT JOIN Category C
ON P.type = C.id
WHERE FIND_IN_SET(P.id,
    (
        SELECT GROUP_CONCAT(ids) FROM
            (SELECT SUBSTRING_INDEX(GROUP_CONCAT(id),',',3) as ids
                FROM Post 
                GROUP BY type
            ) AS foo
        GROUP BY ''
    )
)

这是一个有效的 SQL Fiddle

更新

回应您的评论和更新的问题:

SELECT P.id,P.name,P.description,P.publish_date,C.name 
FROM Post P
LEFT JOIN Category C
ON P.type = C.id
WHERE FIND_IN_SET(P.id,
    (
        SELECT GROUP_CONCAT(ids) FROM
            (SELECT SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY publish_date DESC),',',3) as ids
                FROM Post
                GROUP BY type      
            ) AS foo
        GROUP BY ''
    )
)