統計各位同學每科分數及其總分的實現

1.表結構圖：

 

 

 

2.實現結果圖：

3.建立表：

 

if exists (select * from sysobjects where name='markdb')<br />drop table markdb<br />create table markdb<br />(<br /> mid int identity(1,1) primary key not null,<br /> username varchar(50),<br /> subject varchar(50),<br /> score float<br />)<br />go<br />-----插入測試資料<br />insert markdb (username,subject,score)<br />select '張三','英語',130 union<br />select '李四','語文',100 union<br />select '王五','數學',90 union</p><p>select '張三','語文',100 union<br />select '李四','數學',90 union<br />select '王五','英語',120 union</p><p>select '張三','數學',120 union<br />select '李四','英語','' union<br />select '王五','語文',140<br />

 

方法一：

  case   ...when ...

select username as '姓名',sum(case subject when '語文' then score else null end) as '語文',</p><p>sum(case subject when '數學' then score else null end)as '數學',</p><p>sum(case subject when '英語' then score else null end)as '英語',sum(score) as '總分'</p><p>from markdb group by username<br />

方法二：

   使用pivot函數

select b1. 姓名,語文,數學,英語,d.總分 from (select b.username as 姓名,語文,數學,英語 from (select username,subject,score from markdb)as m<br /> pivot( sum(score) for subject in ([語文],[數學],[英語]))as b )as b1 ,(select username as 姓名,sum(score) as 總分 from markdb group by username) as d<br />where b1.姓名=d.姓名

 

有關pivot函數用法：

pivot 運算建立交叉資料表查詢，把值轉化為多列，使用彙總來根據新列對資料進行分組
  文法：from table_source
          pivot(aggregate_function(value_culumn)
           for pivot_culumn
           in(<culumn_list>)                  
                ) table_alias
說明：  table_source 旋轉資料的表，aggregate_function(value_culumn) 在某列上使用的彙總函式，pivot_culumn 建立列頭的列，
culumn_list 從旋轉列中旋轉的值，table_alias 旋轉結果集的表別名。

 

個人認為第二方法寫的不是很好！看起來難受。