由成绩表写合计问题

Question

select a.username,a.chinese,a.english,a.math,a.total from(select '2' as sortid,username,chinese,english,math,total=chinese+english+math from score
union
select '1' as sortid,'total2',sum(chinese) ,sum(english) ,sum(math),sum(chinese) +sum(english)+sum(math) from score
) as a
order by a.sortid

Answer 1

select
  username,chinese,english,math,total
From
   (select
       username,chinese,english,math,total=(chinese+english+math)
     From
         score
     Group By
         username
     with rollup) a
没测试过

Answer 2

declare @t table(username varchar(20),chinese  int,english int,math int)
insert into @t
select 'total2',206,252,         273 union
select 'li'         ,76          ,72         , 98 union
select 'wang'       ,50          ,95          ,100 union
select 'zhang'      ,80          ,85          ,75 select isnull(username,'汇总') as username,chinese=sum(chinese),english=sum(english),math=sum(math),total=sum(chinese)+sum(english)+sum(math)
from @t 
group by username with rollup/*username chinese english math total
-----------------------------------------------
li 76 72 98 246
total2 206 252 273 731
wang 50 95 100 245
zhang 80 85 75 240
汇总 412 504 546 1462*/

Answer 3

如有username不唯一。则有问题。

Answer 4

不好意思,下面是测试过的保证正确
declare @t table(username varchar(20),chinese  int,english int,math int)
insert into @t
select 'total2',    206,         252,         273 union
select 'li'         ,76          ,72         , 98 union
select 'wang'       ,50          ,95          ,100 union
select 'zhang'      ,80          ,85          ,75 select
  username,chinese,english,math,total=(chinese+english+math)
From
   (select
       username,chinese=sum(chinese),english=sum(english),math=sum(math),total=sum(chinese+english+math)
     From
         @t
     Group By
         username
     with rollup) a

Answer 5

没看到id，按照id分组。username不唯一没有关系。
declare @t table(id int,username varchar(20),chinese  int,english int,math int)
insert into @t
select 1,'total2',206,252,         273 union
select 2,'li'         ,76          ,72         , 98 union
select 3,'wang'       ,50          ,95          ,100 union
select 4,'li'      ,80          ,85          ,75 select id,(case grouping(id) when 1 then '汇总' else max(username) end) as username,chinese=sum(chinese),english=sum(english),math=sum(math),total=sum(chinese)+sum(english)+sum(math)
from @t 
group by id with rollup/*id username chinese english math total
-----------------------------------------------
1 total2 206 252 273 731
2 li 76 72 98 246
3 wang 50 95 100 245
4 li 80 85 75 240
NULL 汇总 412 504 546 1462
*/

Answer 6

同意rivery的方法,唯一不同的就是把汇总放到了后边,而不是前边