1、创建一个用户表：脚本如下create table student( id number primary key, name varchar2(10), course varchar2(10), score number(4,2))
2、插入测试数据：脚本如下insert i艘绒庳焰nto student values(1,'张三','JAVA&垆杪屑丝#39;,90);insert into student values(2,'张三','C',95);insert into student values(3,'赵六','JAVA',85);insert into student values(4,'赵六','C',70);
3、查询表的数据显示效果如图：ID NAME COURSE SCORE--------------------------------------1 张三 JAVA 902 张三 C 953 赵六 JAVA 854 赵六 C 70
4、方法一：使用如下语句查询：select name,sum(decode(course,'JAVA',score)) JAVA,sum(decode(course,'C',score)) Cfrom studentgroup by name;效果如下：NAME JAVA C--------------------张三 90 95赵六 85 70
5、方法二：select name,sum(case when course='JAVA' then score else 0 end) as java,sum(case when course='C' then score else 0 end) as Cfrom studentgroup by name;效果同上
6、方法三：select t.name,sum(t.JAVA) java,sum(t.C) cfrom ( select name,case when course='JAVA' then score else 0 end java, case when course='C' then score else 0 end C from student) tgroup by name;效果同上