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