[color=green]--建视图 vReContentCount, 该视图包含每篇文章与相应的评论文章数,并包含发表日期和评论文章数、点击量。[/color]
create view vReContentCount as --创建视图
select c.cTitle, count(r.gid) as ts ,c.sDatetime , c.chits --这里面注意有一个要计算评论数的,不能直接加在后面,所以要有group by
from reGBook as r , content as c --有两张表
where c.cID = r.cID
group by c.cTitle ,c.sDatetime ,c.chits --这里面可以给出多个值
[color=green]--建一个存储过程 ap_QryAuthor,参数为 cAuthor,用于返回某作者的所有文章,返回为一个表,
--字段有该作者发表文章的栏目,在该栏目发表的文章篇数,在该栏目发表的文章的总的评论文章数。[/color]
CREATE PROCEDURE ap_QryAuthor @getAthor varchar(20)
AS
BEGIN
declare @Athor varchar(20);
set @Athor = @getAthor
select i.itemName , count(distinct c.cID) as articNum , count(r.gid) --distinct 用来显示无重复列
from item as i ,reGBook as r , content as c
where i.itemID = c.itemID and c.cID = r.cID
and cAuthor = @Athor
group by i.itemName
END
GO
exec ap_QryAuthor '234'
create view vReContentCount as --创建视图
select c.cTitle, count(r.gid) as ts ,c.sDatetime , c.chits --这里面注意有一个要计算评论数的,不能直接加在后面,所以要有group by
from reGBook as r , content as c --有两张表
where c.cID = r.cID
group by c.cTitle ,c.sDatetime ,c.chits --这里面可以给出多个值
[color=green]--建一个存储过程 ap_QryAuthor,参数为 cAuthor,用于返回某作者的所有文章,返回为一个表,
--字段有该作者发表文章的栏目,在该栏目发表的文章篇数,在该栏目发表的文章的总的评论文章数。[/color]
CREATE PROCEDURE ap_QryAuthor @getAthor varchar(20)
AS
BEGIN
declare @Athor varchar(20);
set @Athor = @getAthor
select i.itemName , count(distinct c.cID) as articNum , count(r.gid) --distinct 用来显示无重复列
from item as i ,reGBook as r , content as c
where i.itemID = c.itemID and c.cID = r.cID
and cAuthor = @Athor
group by i.itemName
END
GO
exec ap_QryAuthor '234'