select top 20 * from Diary a join Comment b on a.DiaryId=b.DiaryId order by a.Visits desc ,b.CommentId desc
select top 20 DiaryId,sum(Visits) as TotalVisits,sum(case when CommentId is not null then 1 else 0 end) as Comments from Diary a left join Comment b on a.DiaryId=b.DiaryId group by a.DiaryId order by 2,3
select top 20 m.DiaryId , m.Visits , isnull(count(1),0) 评论数量 from Diary m left join Comment n on m.DiaryId = n.DiaryId group by m.DiaryId , m.Visits order by m.Visits desc , 评论数量 desc
select top 20 * from diary a order by visits,(select count(*) from comment where diaryid=a.diaryid)
select top 20 * from diary a order by visits desc,(select count(*) from comment where diaryid=a.diaryid)desc
--分别 select top 20 m.DiaryId , m.Visits , isnull(count(1),0) 评论数量 from Diary m left join Comment n on m.DiaryId = n.DiaryId group by m.DiaryId , m.Visits order by m.Visits desc , 评论数量 desc--合起 select top 20 t1.DiaryId,t1.Visits+t2.comment_cn as num from Diary t1 left join (select DiaryId,count(*) comment_cn from Comment group by DiaryId) t2 on t1.DiaryId=t2.DiaryId order by num desc
from Diary a join Comment b on a.DiaryId=b.DiaryId
order by a.Visits desc ,b.CommentId desc
from Diary a left join Comment b on a.DiaryId=b.DiaryId
group by a.DiaryId
order by 2,3
from Diary m left join Comment n
on m.DiaryId = n.DiaryId group by m.DiaryId , m.Visits
order by m.Visits desc , 评论数量 desc
--分别
select top 20 m.DiaryId , m.Visits , isnull(count(1),0) 评论数量
from Diary m left join Comment n
on m.DiaryId = n.DiaryId
group by m.DiaryId , m.Visits
order by m.Visits desc , 评论数量 desc--合起
select top 20 t1.DiaryId,t1.Visits+t2.comment_cn as num
from Diary t1 left join
(select DiaryId,count(*) comment_cn from Comment group by DiaryId) t2
on t1.DiaryId=t2.DiaryId
order by num desc