sql语句 实现两个表查询显示,有一个聚合函数count（）

Question

我有一个news表和一个comment表，我想查询news的所有内容和每条new的评论总数，该怎么写？我是用newid连接两个表的

"select top 10 n.newId,count(c.*) from new as n join comment as c on c.newId=n.newId where n.newId=@id group by n.newId order by count(c.*) desc";

这样貌似不对

Answer 1

首先这个应该是外连接查询。你知道你错在何处吗?你要查询所有的news，为什么还要限定n.newsid=@ID,你这样查询的只有一条记录了。
select top 10 n.newid,count(c.*) from new as n left jion conmment as c
on n.newid=c.newid group by n.newid order by count(c.*) desc

Answer 2

这个我们首先可以对2张表按newsid进行连接，
然后按new进行分组计数即可。

select n.newId, count(c.*)
from new as n
left join comment as c on c.newId = n.newId
group by n.newId

Answer 3

select top 10 n.*,d.p from new n, (select count(*) as p,newId from comment group by newId) d where n.newId=@id and n.newId = d.newId order by d.p desc本回答被提问者采纳

Answer 4

select n.newId, count(c.*)
from new as n
left join comment as c on c.newId = n.newId
group by n.newId
order by count(c.*) desc

应该没问题的。

Answer 5

select
News.*
,C.Num
from
News
AS N
inner
join
(
select
NewID,count(*)
AS
Num
from
comment group
by
NewID
)
AS
C
on
N.ID=C.NewIDorder
by
C.Num
DESC