数据库题：行转列

create table tb_stu_info(

id number(4) ,

name varchar2(20),

course varchar2(20),

score number(5,2)

);

insert into tb_stu_info values(1,'张三','语文',81);

insert into tb_stu_info values(2,'张三','数学',75);

insert into tb_stu_info values(3,'李四','语文',86);

insert into tb_stu_info values(4,'李四','数学',90);

insert into tb_stu_info values(5,'王五','语文',81);

insert into tb_stu_info values(6,'王五','数学',100);

insert into tb_stu_info values(7,'王五','英语',90);

commit;

需要达成的目标形式：

---

--看目标图片分析。每个人有语文、数学、英语。所以需要将原数据按姓名分组

--分组

select name from tb_stu_info group by name

---

--成绩该怎么显示呢？

--分析,推演

select name ,decode(course, '语文', score) "语文" from tb_stu_info

---

--继续，推演

select name ,

decode(course, '语文' , score) "语文",

decode(course, '数学' , score) "数学",

decode(course, '英语' , score) "英语"

from tb_stu_info

---

--然后，分组，按姓名分组

select name ,

min(decode (course, '语文', score)) "语文",

min(decode (course, '数学', score)) "数学",

min(decode (course, '英语', score)) "英语"

from tb_stu_info group by name

---

---

---

---

总结 ：利用 分组+decode函数

select name ,

min (decode (course, '语文', score)) "语文",

min (decode (course, '数学', score)) "数学",

min (decode (course, '英语', score)) "英语"

from tb_stu_info group by name

---

---

---

面试题2：

查询出每门课都大于80分的学生名字，分数，课程。

--每门课分数都大于80

--统计课程数

select count (distinct course) cn from tb_stu_info

--查询每门课大于80分-->一个人的分数最低的>80即可-->按学生分组、然后过滤组()

select name ,min(score), count(*) from tb_stu_info group by name having min(score)>80 ;

--综合。注意，课程数必须要对应。。count(*)是课程数

select name ,min(score), count(*) from tb_stu_info group by name having min(score)>80

and count (*)=(select count(distinct course) cn from tb_stu_info);