sql how to sort based on 'number' in other table

Question

The scene is like this
There is an article table article
field: aid content
There is also a like table praise field: id aid time
The aid field of the like table stores the corresponding Article aid
Now I want to get the list of articles but sort them from large to small according to the number of likes. How to write this SQL?
Thank you.

Answer 1

If the amount of data is large, left join is relatively slow. If it is displayed in pages or just asks for the data of the first few dozen items, you can first ask for the sorted aids in the likes table, and then find the articles corresponding to these aids in the article table

Answer 2

select a.content from article a left join praise b on a.aid=b.aid order by b.time desc

Answer 3

SELECT
  a.aid,
  a.content,
  pr.praiseCount
FROM article a
  LEFT JOIN (SELECT
               p.aid,
               count(1) AS praiseCount
             FROM praise p
             GROUP BY p.aid) pr
    ON a.aid = pr.aid
ORDER BY pr.praiseCount DESC

Answer 4

select a.aid,count(p.aid) num from article a left join praise p on a.aid=p.aid group by p.aid order by num desc;