sql语句实现行转列练习

sql语句实现行转列练习
强烈推介IDEA2021.1.3破解激活,IntelliJ IDEA 注册码,2021.1.3IDEA 激活码  

大家好,我是架构君,一个会写代码吟诗的架构师。今天说一说sql语句实现行转列练习,希望能够帮助大家进步!!!

 

create table STUDENT_SCORE
(
name VARCHAR2(20),
subject VARCHAR2(20),
score NUMBER(4,1)
);
insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '语文', 78.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '数学', 88.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '英语', 98.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '语文', 89.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '数学', 76.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '英语', 90.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '语文', 99.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '数学', 66.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '英语', 91.0);

select * from student_score;

/*
4.1得到类似下面的结果
姓名 语文 数学 英语
王五 89 56 89
李四 xx xx xx

*/

数据库查询中用到的关键词主要包含六个,他们的顺序依次为 
 
select--from--where--group by--having--order by 

语句的执行顺序

 from--where--group by--having--select--order by,

--连表查询

select t1.name "姓名", t1.score "语文", t2.score "数学", t3.score "英语"
from (select name, score from student_score where subject = '语文') t1,
(select name, score from student_score where subject = '数学') t2,
(select name, score from student_score where subject = '英语') t3
where t1.name = t2.name
and t1.name = t3.name;

思路

通过观察结果,先把每列的结果查询出来,在把每个子表关联起来。

--分组查询
select name "姓名",
max(decode(subject, '语文', score)) "语文",
max(decode(subject, '数学', score)) "数学",
max(decode(subject, '英语', score)) "英语"
from student_score
group by name;

 

思路

用name分组 获得每个人的成绩,每组数据在通过decode匹配指定的数据,然后通过max函数过滤 输出

 

posted on
2019-04-18 20:46
QM.C 阅读(
...) 评论(
...)
编辑
收藏

转载于:https://www.cnblogs.com/cqming/p/10732245.html

本文来源weixin_30595035,由架构君转载发布,观点不代表Java架构师必看的立场,转载请标明来源出处:https://javajgs.com/archives/29888

发表评论