多列组合行

[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'