MSSQL - 存储过程取出5条热点新闻

USE [DB_News]
GO
/****** Object:  StoredProcedure [dbo].[SelectHotNews]    Script Date: 2015/7/8 13:34:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		HF_Ultrastrong
-- Create date: 2015年7月5日16:40:20
-- Description:	取出10条热点新闻 （热点新闻：评论最多）
-- =============================================
ALTER PROCEDURE [dbo].[SelectHotNews]
AS
BEGIN
	select top 5 n.ID, n.Title, n.CreateTime, c.Name, count(t.ID) as CountNumber
	from Tb_News as n
	inner join Tb_Category as c on n.CaId = c.ID
	--用左连接，这样可以查询出评论为0的新闻
	left join Tb_Comment as t on t.NewsId = n.ID
	group by n.ID, n.Title, n.CreateTime, c.Name
	order by CountNumber desc
END

 

其中涉及到三张表，分类表，新闻表，评论表。

 

最终取出效果：