--示例数据
CREATE TABLE [dbo].[student] (
[xh] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[bj] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[xm] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
insert student select 1,1,'张三'
union all select 2,1,'李四'
union all select 1,2,'王五'
GO
CREATE TABLE [dbo].[cj] (
[xh] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[bj] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[km] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[cj] [float] NULL ,
[begindate] [datetime] NULL ,
[enddate] [datetime] NULL
) ON [PRIMARY]
insert cj select 1,1,'语文',93,'2003-1-1','2003-11-1'
union all select 1,1,'数学',92,'2003-1-1','2003-11-1'
union all select 1,2,'语文',90,'2003-1-1','2003-11-1'
union all select 1,2,'数学',97,'2003-1-1','2003-11-1'
union all select 2,1,'语文',95,'2003-1-1','2003-11-1'
union all select 2,1,'数学',80,'2003-1-1','2003-11-1'
GO
CREATE TABLE [dbo].[bj] (
[bjbh] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[bjmc] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
insert bj select 1,'1班'
union all select 2,'2班'
GO
--查询的存储过程
create proc p_qry
@begindate datetime,
@enddate datetime
as
select 班级=case
when grouping(a.bjmc)=1
then '总计'
when grouping(b.xh)=1
then '合计'
else a.bjmc end
,学号=isnull(cast(b.xh as varchar),'')
,姓名=isnull(b.xm,'')
,语文=cast(sum(case c.km when '语文' then cj else 0 end) as decimal(10,2))
,数学=cast(sum(case c.km when '数学' then cj else 0 end) as decimal(10,2))
from bj a,student b,cj c
where a.bjbh=b.bj
and a.bjbh=c.bj
and b.xh=c.xh
and c.begindate>=@begindate
and c.enddate<=@enddate
group by a.bjmc,b.xh,b.xm with rollup
having grouping(b.xm)=0
or grouping(a.bjmc)=1
or grouping(b.xh)=1
go
--调用
exec p_qry '2003-1-1','2003-11-1'
go
--删除测试
drop table cj,bj,student
drop proc p_qry
/*--测试结果
班级 学号 姓名 语文 数学
---------- ------ --------- ----------- ----------
1班 1 张三 93.00 92.00
1班 2 李四 95.00 80.00
合计 188.00 172.00
2班 1 王五 90.00 97.00
合计 90.00 97.00
总计 278.00 269.00
（所影响的行数为 6 行）
--*/
