效果如圖所示:
測試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
