oracle 横表转纵表 cube函数及union all用法

最新推荐文章于 2021-04-08 13:58:43 发布

pancocoa

最新推荐文章于 2021-04-08 13:58:43 发布

阅读量887

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle 文章标签： oracle sql cube union all 横转纵

本文链接：https://blog.youkuaiyun.com/pancocoa/article/details/51774373

oracle 专栏收录该内容

1 篇文章

订阅专栏

本文通过SQL查询展示了如何从一个包含学生姓名、课程及分数的数据表中进行成绩统计与分析。包括整体平均分、各科目平均分以及每位学生的具体得分情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--fzq
create table fzq
(
id varchar(2),
name varchar(10),
course varchar(20),
score number(3)
);

insert into fzq values ('1','张三','语文',80);
insert into fzq values('2','张三','数学',70);
insert into fzq values ('3','李四','语文',40);
insert into fzq values ('4','李四','数学',100);
insert into fzq values ('5','王五','语文',90);
insert into fzq values ('6','王五','数学',92);

--方法一

--0
select * from fzq;

ID NAME COURSE SCORE
-- ---------- -------------------- ----------
1 张三语文 80
2 张三数学 70
3 李四语文 40
4 李四数学 100
5 王五语文 90
6 王五数学 92

--1
select name, course, round(avg(score),1) score
from fzq
group by cube(name,course);

NAME COURSE SCORE
---------- -------------------- ----------
78.7
数学 87.3
语文 70
李四 70
李四数学 100
李四语文 40
王五 91
王五数学 92
王五语文 90
张三 75
张三数学 70
张三语文 80

--2
select name 姓名,
(case when course = '语文' then score else 0 end) as 语文,
(case when course = '数学' then score else 0 end) as 数学
from(
select name, course, round(avg(score),1) score
from fzq
group by cube(name,course)
);

姓名语文数学
---------- ---------- ----------
0 0
0 87.3
70 0
李四 0 0
李四 0 100
李四 40 0
王五 0 0
王五 0 92
王五 90 0
张三 0 0
张三 0 70
张三 80 0

--3
select coalesce(name,'平均分') 姓名,
max(case when course = '语文' then score else 0 end) as 语文,
max(case when course = '数学' then score else 0 end) as 数学
from(
select name, course, round(avg(score),1) score
from fzq
group by cube(name,course)
)
group by name;

姓名语文数学
---------- ---------- ----------
李四 40 100
王五 90 92
张三 80 70
平均分 70 87.3

--方法二

--0
select * from fzq;

ID NAME COURSE SCORE
-- ---------- -------------------- ----------
1 张三语文 80
2 张三数学 70
3 李四语文 40
4 李四数学 100
5 王五语文 90
6 王五数学 92

--1
select name 姓名,
(case when course='语文' then score else 0 end) as 语文,
(case when course='数学' then score else 0 end) as 数学
from fzq;

姓名语文数学
---------- ---------- ----------
张三 80 0
张三 0 70
李四 40 0
李四 0 100
王五 90 0
王五 0 92

--2
select name 姓名,
max(case when course='语文' then score else 0 end) as 语文,
max(case when course='数学' then score else 0 end) as 数学
from fzq
group by name
union all
select '平均分',avg(cn),round(avg(math),1)from(
select name,
max(case when course='语文' then score else 0 end) as cn,
max(case when course='数学' then score else 0 end) as math
from fzq
group by name);

姓名语文数学
---------- ---------- ----------
王五 90 92
李四 40 100
张三 80 70
平均分 70 87.3