时间:2021-07-01 10:21:17 帮助过:3人阅读
用decode函数,或者case when实现行转列 Oracle ----创建测试表create table student_score( name varchar2(20), subject varchar2(20), score number(4,1) ); -----插入测试数据insert into student_score (name,subject,score)values('张三','语文',78); in
用decode函数,或者case when实现行转列----创建测试表 create table student_score( name varchar2(20), subject varchar2(20), score number(4,1) ); -----插入测试数据 insert into student_score (name,subject,score)values('张三','语文',78); insert into student_score (name,subject,score)values('张三','数学',88); insert into student_score (name,subject,score)values('张三','英语',98); insert into student_score (name,subject,score)values('李四','语文',89); insert into student_score (name,subject,score)values('李四','数学',76); insert into student_score (name,subject,score)values('李四','英语',90); insert into student_score (name,subject,score)values('王五','语文',99); insert into student_score (name,subject,score)values('王五','数学',66); insert into student_score (name,subject,score)values('王五','英语',91); -----decode行转列 select name "姓名", sum(decode(subject, '语文', nvl(score, 0), 0)) "语文", sum(decode(subject, '数学', nvl(score, 0), 0)) "数学", sum(decode(subject, '英语', nvl(score, 0), 0)) "英语" from student_score group by name; ------ case when 行转列 select name "姓名", sum(case when subject='语文' then nvl(score,0) else 0 end) "语文", sum(case when subject='数学' then nvl(score,0) else 0 end) "数学", sum(case when subject='英语' then nvl(score,0) else 0 end) "英语" from student_score group by name;