前期准备
创建一个表
create table student_score(
name varchar2(30),
subject varchar(20),
score number(4,1)
);
插入数据
insert into student_score (name,subject,score)values('张三','Chinese',90);
insert into student_score (name,subject,score)values('张三','Mathematics',80);
insert into student_score (name,subject,score)values('张三','English',79);
insert into student_score (name,subject,score)values('li shi','Chinese',96);
insert into student_score (name,subject,score)values('li shi','Mathematics',86);
insert into student_score (name,subject,score)values('li shi','English',76);
insert into student_score (name,subject,score)values('wang wu','Chinese',92);
insert into student_score (name,subject,score)values('wang wu','Mathematics',82);
insert into student_score (name,subject,score)values('wang wu','English',72);
以下命令
用decode命令
select name,
sum(decode(subject, 'Chinese', nvl(score, 0), 0)) "Chinese",
sum(decode(subject, 'Mathematics', nvl(score, 0), 0)) "Mathematics",
sum(decode(subject, 'English', nvl(score, 0), 0)) "English"
from student_score
group by name;
获得结果:
NAME Chinese Mathematics English
------------------------------ ---------- ----------- ----------
张三 90 80 79
wang wu 92 82 72
li shi 96 86 76
用case then命令
SELECT NAME,
SUM(CASE WHEN SUBJECT='CHINESE'
THEN NVL(SCORE,0)
ELSE 0
END) "CHINESE",
SUM(CASE WHEN SUBJECT='MATHEMATICS'
THEN NVL(SCORE,0)
ELSE 0
END) "MATHEMATICS",
SUM(CASE WHEN SUBJECT='ENGLISH'
THEN NVL(SCORE,0)
ELSE 0
END) "ENGLISH"
FROM STUDENT_SCORE
GROUP BY NAME;
使用decode判断字符串是否一样
DECODE(value,if 条件1,then 值1,if 条件2,then 值2,...,else 其他值)