MySQL -每个职类5个员额

Question

我有这样一个模式：

Posts (id, title, created_on)
Categories (id, title, description)
Distributions (id, post_id*, category_id, main)

posts表中的每个帖子都有一个或多个类别，只有一个主类别。

我想获得每个职类的5个员额，条件如下：

• 所有职位必须超过90天
• 对于每个类别，文章按DESC中的created_on排序。

这就是我所想的，但却无法正确地做到：

SELECT p.title, c.title, c.description FROM posts p, distributions d, categories c
  WHERE p.id = d.post_id
  AND d.category_id = c.id
  AND d.main = 1
  AND ABS(DATEDIFF(NOW(), p.created_on)) > 90
  GROUP BY c.id
  ORDER BY p.created_on DESC
  LIMIT 5;

我不确定limit 5是返回联接表的前5行，还是返回每个类别组的5行？

任何解释都将不胜感激。谢谢!

Answer 1

使用MySQL中的会话变量，因为它目前不支持任何解析函数。下面的子查询将为每个category_title生成序列号，并使用该列在外部查询中进行筛选。

SELECT  post_title, category_title, description
FROM
    (
        SELECT  p.title AS post_title, 
                c.title AS category_title, 
                c.description 
                @counter := IF(@current_category = c.title, @counter + 1, 1) AS counter,
                @current_category := c.title
        FROM    posts p, distributions d, categories c
        WHERE   p.id = d.post_id
                AND d.category_id = c.id
                AND d.main = 1
                AND ABS(DATEDIFF(NOW(), p.created_on)) > 90
        ORDER   BY p.created_on DESC
    ) s
WHERE   counter <= 5

虽然结构是不一样的，但是这个演示将显示查询的结果。

Answer 2

select c.id,c.title,p.id,p.title,description,created_on from Categories as c
join Distributions as d on d.category_id = c.id
join Posts as p on p.id = d.post_id 
where ABS(DATEDIFF(NOW(), p.created_on)) > 90
ORDER BY p.created_on DESC
LIMIT 5;