oracle的面试题目简化结果集

create table tb_course(
name varchar2(18),
course varchar2(18),
grade number
);


INSERT INTO tb_course(NAME,course,grade) VALUES('tom','JDBC',20);
INSERT INTO tb_course(NAME,course,grade) VALUES('tom','Hibernate',50);
INSERT INTO tb_course(NAME,course,grade) VALUES('tom','Spring',80);
INSERT INTO tb_course(NAME,course,grade) VALUES('mary','JDBC',30);
INSERT INTO tb_course(NAME,course,grade) VALUES('mary','Hibernate',60);

INSERT INTO tb_course(NAME,course,grade) VALUES('mary','Spring',70);

--查询

select  * from tb_course;

--结果

1 tom JDBC 20
2 tom Hibernate50
3 tom Spring80
4 mary JDBC 30
5 mary Hibernate 60
6 mary Spring 70

接下来要做的事简化结果：

方法一，多表链接查询

select T4.name,T1.JDBC,T2.Hibernate,T3.Spring from 
(select name ,grade as JDBC from tb_course where course='JDBC') T1,
(select name,grade  as Hibernate from tb_course where course='Hibernate') T2 ,
(select name, grade  as Spring from tb_course  where course='Spring') T3 ,
(select distinct name from tb_course) T4
where T4.name=T1.name and T4.name=T2.name and T4.name=T3.name ;

--结果

1 tom 20 50 80
2 mary 30 60 70

方法二:

使用函数

SELECT 
      NAME,
      DECODE(course,'JDBC',grade,0) JDBC,
      DECODE(course,'Hibernate',grade,0) Hibernate,
      DECODE(course,'Spring',grade,0) Spring
      FROM tb_course;
      
--decode (column,value,columnvalue,default)
SELECT 
      NAME,
      SUM(DECODE(course,'JDBC',grade,0) )JDBC,
      SUM(DECODE(course,'Hibernate',grade,0))  Hibernate,
      SUM(DECODE(course,'Spring',grade,0) ) Spring
FROM tb_course
GROUP BY NAME;

结果:

1 tom 20 50 80
2 mary 30 60 70