sql统计

工作中遇到一个问题，需要对一个表每个用户处理的数据进行一

drop table stuscore

CREATE TABLE stuscore (  

  id int  NOT NULL  ,  

  name varchar(20) default NULL,  

  subject varchar(20) default NULL,  

  score int default NULL,  

  stuid varchar(10) default NULL,  

  PRIMARY KEY  (id)  

)

insert  into stuscore(id,name,subject,score,stuid) values    

(1,'张三','数学','89','1'),  

(2,'张三','语文','80','1'),  

(3,'张三','英语','70','1'),  

(4,'李四','数学','90','2'),  

(5,'李四','语文','70','2'),  

(6,'李四','英语','80','2');  

select * from stuscore

 

select stuid as 学号,name as 姓名,  

--sum(case when subject='数学' then score+5 else 0 end) as 加分,  

sum(case when subject='语文' then score else 0 end) as 语文,  

sum(case when subject='数学' then score else 0 end) as 数学,  

sum(case when subject='英语' then score else 0 end) as 英语,  

sum(score) as 总分,(sum(score)/count(*)) as 平均分  

from stuscore  

group by stuid,name    

order by 总分 desc

-->