随笔 - 11  文章 - 0 评论 - 2 trackbacks - 0

1.建表:学生表(姓名,学科,成绩)

CREATE TABLE teststudent(
    stuname varchar(50) NULL,
    subjects varchar(50) NULL,
    source int NULL
)
drop table teststudent ;
select * from teststudent ;
delete from teststudent ;

2.准备数据:

insert into teststudent(stuname,subjects,source) values('小明','语文',80); 
insert into teststudent(stuname,subjects,source) values('小明','数学',85); 
insert into teststudent(stuname,subjects,source) values('小明','英语',90); 
insert into teststudent(stuname,subjects,source) values('小红','语文',86); 
insert into teststudent(stuname,subjects,source) values('小红','数学',90); 
insert into teststudent(stuname,subjects,source) values('小红','英语',85); 
insert into teststudent(stuname,subjects,source) values('小亮','语文',99); 
insert into teststudent(stuname,subjects,source) values('小亮','数学',99); 
insert into teststudent(stuname,subjects,source) values('小亮','英语',100);

 

3.转换:可以采用max()、sum()两种方式

--方式1:
select stuname ,
MAX(case when subjects = '语文' then source else 0 end ) '语文',
MAX(case when subjects = '数学' then source else 0 end ) '数学',
MAX(case when subjects = '英语' then source else 0 end ) '英语'
from teststudent group by stuname ;

--方式2:
select stuname ,
    SUM(case when subjects = '语文' then source else 0 end) '语文',
    SUM(case when subjects = '数学' then source else 0 end ) '数学',
    SUM(case when subjects = '英语' then source else 0 end ) '英语'
from teststudent group by stuname ;

posted on 2018-10-22 17:12  沈超  阅读(...)  评论(...编辑  收藏