如何进行sql查询？

Question

以下是表的结构，喜欢

id(int 11)
idn(varchar 10)
type (enum ('up','down'))

以下是表News的结构

id(int 11)
idn(varchar 10)
header (varchar 100)
date(datetime)

要显示新闻，我使用sql查询：

SELECT header FROM News n left join Likes l on l.idn=n.idn ORDER by n.date

但是，现在我想从表新闻中选择标题新闻，并按desc排序，用type = up来计数表中喜欢的行。

我该怎么做？

Answer 1

这里有一个查询，用于获取每天最喜欢的标题。如果所有日期都需要顶部，请从n.date中删除ORDER BY

SELECT header FROM News as n 
LEFT JOIN
(
  Select idn,Count(*) as UpLikes FROM Likes  WHERE type='up' GROUP BY idn
) as l 
   ON l.idn=n.idn 
ORDER BY n.date,l.UpLikes DESC

Answer 2

SELECT n.header, COUNT(i.`id`) as like_count 
FROM Likes l LEFT JOIN News n ON n.idn = l.idn 
WHERE  l.`type` = 'up' 
ORDER BY like_count DESC

Answer 3

你为什么不用这个：

    SELECT id, idn, header, LikeCount
    FROM 
    (
       SELECT id, idn, header, (SELECT COUNT(1) FROM [Likes]) AS [LikeCount] 
       FROM [News]    
    )
   ORDER By [LikeCount]