oracle 行变列问题

成绩表数据聚合

最新推荐文章于 2024-07-22 17:18:58 发布

jiangpandubu03

最新推荐文章于 2024-07-22 17:18:58 发布

阅读量1.2k

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle 文章标签： oracle insert oracle10g sql 存储 java

本文链接：https://blog.youkuaiyun.com/jiangpandubu03/article/details/6320764

oracle 专栏收录该内容

0 篇文章

订阅专栏

有一张成绩表，表结构和数据如下：

姓名：name	课程： course	成绩：score
张三	语文	89
张三	数学	90
张三	英语	78
李四	语文	97
李四	数学	69
李四	英语	79
王五	英语	89

问：如何设计sql，查询到如下结果：

姓名：name	语文	数学	英语
张三	89	90	78
李四	97	69	79
王五	0	0	89

建表sql如下：

create table tbl(
name varchar2(20),
course varchar2(20),
score number(3)
);
insert into tbl(name,course,score) values('张三','语文','89');
insert into tbl(name,course,score) values('张三','数学','90');
insert into tbl(name,course,score) values('张三','英语','78');
insert into tbl(name,course,score) values('李四','语文','97');
insert into tbl(name,course,score) values('李四','数学','69');
insert into tbl(name,course,score) values('李四','英语','79');

insert into tbl(name,course,score) values('王五','英语','89');
commit;

这是一个典型的行变列的问题，

1、固定列数的行变列

根据“姓名”可以将源表中的若干记录合并成一条记录，那么就用“姓名”字段分组，再统计每一组中课程的成绩。

select name,
sum(decode(course, '语文', score, 0)) 语文,
sum(decode(course, '数学', score, 0)) 数学,
sum(decode(course, '英语', score, 0)) 英语
from tbl
group by name;

decode函数相当于条件语句(if)，可以将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。

根据course字段的值和“语文”匹配，如果相同则返回course字段的值，如果不相同或者为空则返回0。

因为使用了分组，所以要使用sum函数。

也可以使用case……when……then语句。

select name,
max(case course when '语文' then score else 0 end) as 语文,
max(case course when '数学' then score else 0 end) as 数学,
max(case course when '英语' then score else 0 end) as 英语
from tbl
group by name

2、不固定列数的行变列

如果课程是不固定的，那么就拼不出上面的sql，这种情况比较复杂，在Oracle中我们引入pl/sql编写存储过程来解决这个问题。

大概思路是，在packages中声明一个游标类型，用于在存储过程中返回游标类型的记录；在存储过程中查询考试科目，动态拼sql；用sql声明游标返回。

--packages中声明一个游标类型
CREATE OR REPLACE PACKAGE pkg_getrecord
IS
TYPE myrctype IS REF CURSOR;
END pkg_getrecord;

create or replace procedure myprocedure (c out pkg_getrecord.myrctype )as
--查找所有考试科目
CURSOR cur IS select distinct course from tbl;
sqlStr varchar2(1000);
begin
sqlStr:='select name ';
--循环游标，将考试科目拼写成sql条件
for mr in cur loop
sqlStr:=sqlStr || ',sum(decode(course, ''';
sqlStr:=sqlStr || mr.course;
sqlStr:=sqlStr || ''', score, 0)) ';
sqlStr:=sqlStr || mr.course;
end loop;
sqlStr:=sqlStr || ' from tbl group by name';
--返回游标
open c for sqlStr;
end myprocedure;