mysql - SQL:如何从按类别排序的多篇文章中获取浏览量？

Question

我使用 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/

Answer 1

由于 Gordon 现在似乎处于离线状态，我将在此处发布我的编辑作为另一个答案。这应该给你你想要的顺序。

    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;