效果如图所示:
测试sql语句如下:
代码如下:
declare @tab table(class varchar(20),student varchar(20),course varchar(50),quantity decimal(7,2));
insert into @tab(class,student,course,quantity) values('a班','张三','语文',60);
insert into @tab(class,student,course,quantity) values('a班','张三','数学',70);
insert into @tab(class,student,course,quantity) values('a班','张三','英语',80);
insert into @tab(class,student,course,quantity) values('a班','李四','语文',30);
insert into @tab(class,student,course,quantity) values('a班','李四','数学',40);
insert into @tab(class,student,course,quantity) values('a班','李四','英语',50);
insert into @tab(class,student,course,quantity) values('b班','王五','语文',65);
insert into @tab(class,student,course,quantity) values('b班','王五','数学',75);
insert into @tab(class,student,course,quantity) values('b班','王五','英语',85);
insert into @tab(class,student,course,quantity) values('b班','赵六','语文',35);
insert into @tab(class,student,course,quantity) values('b班','赵六','数学',45);
insert into @tab(class,student,course,quantity) values('b班','赵六','英语',55);
select * from @tab
select
(case when grouping(class)=1 then '总平均' when grouping(student)=1 then '' else class end ) as class
,(case when grouping(class)=1 then '' when grouping(student)=1 then '平均' else student end) as student
,avg(语文) as 语文
,avg(数学) as 数学
,avg(英语) as 英语
,avg(总分) as 总分
from (
select class,student
,(select isnull(sum(quantity),0) from @tab where class=t.class and student=t.student and course='语文') as '语文'
,(select isnull(sum(quantity),0) from @tab where class=t.class and student=t.student and course='数学') as '数学'
,(select isnull(sum(quantity),0) from @tab where class=t.class and student=t.student and course='英语') as '英语'
,(select isnull(sum(quantity),0) from @tab where class=t.class and student=t.student) as '总分'
from @tab as t
group by class,student
) as temptab
group by class,student,语文,数学,英语,总分 with rollup
having grouping(语文)=1
and grouping(数学)=1
and grouping(英语)=1