我使用MySQL,我有一个代码可以从每个类别中获取最后6篇文章:
select a.*
from article a
where a.created >= coalesce((select a2.created
from article a2
where a2.category = a.category
order by a2.created desc
limit 5, 1
), a.created
);
现在,我还需要从另一个表中获取每篇文章的总视图。怎么做?这不起作用:
select a.*, Count(view.*) as CountViews
from article a
where a.created >= coalesce((select a2.created
from article a2
where a2.category = a.category
order by a2.created desc
limit 5, 1
), a.created
) left join view on a.id = view.post_id;
按类别打印文章的示例:https://nedvigimostmsk.ru/
最佳答案
我可能建议使用子查询:
select a.*,
(select count(*)
from views v
where a.id = v.post_id
) as num_views
from article a
where a.created >= coalesce((select a2.created
from article a2
where a2.category = a.category
order by a2.created desc
limit 5, 1
), a.created
);
查询的问题是您正在使用
COUNT()
而没有GROUP BY
。要么返回错误(在最新版本的MySQL中使用默认设置),要么返回一个神秘的行。您可以使用GROUP BY
修复此问题,但是关联的子查询通常具有更好的性能-尤其是使用正确的索引。关于mysql - SQL:如何从按类别排序的多篇文章中获得观看次数计数?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/51198638/