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
其中涉及到三张表,分类表,新闻表,评论表。
最终取出效果: