SQL:如何从多篇文章中获取按类别排序的浏览量？
SQL:如何从多篇文章中获取按类别排序的浏览量？
提问于 2018-07-06 03:32:05
我使用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/
回答 4
Stack Overflow用户
回答已采纳
发布于 2018-07-06 03:36:13
我可能建议使用子查询：
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
);查询的问题是您使用的是没有GROUP BY的COUNT()。这要么返回一个错误(在较新版本的MySQL中使用默认设置)，要么返回一个神秘的行。您可以使用GROUP BY解决这个问题，但是相关子查询通常具有更好的性能--尤其是使用正确的索引。
Stack Overflow用户
发布于 2018-07-06 04:42:15
由于戈登现在似乎离线了，我将在这里发布我的编辑作为另一个答案。这应该会给你你想要的顺序。
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
)
ORDER BY a.created DESC;Stack Overflow用户
发布于 2018-07-06 03:40:07
除了戈登之外，您还可以对从views表进行计数和分组的嵌套查询进行连接
select a.*, v.cnt as total_views
from article a
inner join
(
select post_id, count(*) as cnt from views group by post_id
) v on a .id = v.post_id
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
);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51198638
复制相关文章
相似问题