Oracle 行转列实例二

上篇文章在行转列时给出了两种方法，现在给出另外的两种方法。
https://www.jianshu.com/p/7663fada0726

方法三

-- join

select ss.name, ss.score from student_score ss where ss.subject = '语文'
select ss.name, ss.score from student_score ss where ss.subject = '数学'
select ss.name, ss.score from student_score ss where ss.subject = '英语'

select ss01.name 姓名, ss01.score 语文, ss02.score 数学, ss03.score 英语 from (
       select ss.name, ss.score from student_score ss where ss.subject = '语文'
) ss01 join (select ss.name, ss.score from student_score ss where ss.subject = '数学') ss02 on ss01.name = ss02.name
join (select ss.name, ss.score from student_score ss where ss.subject = '英语') ss03 on ss01.name = ss03.name

方法四

-- union all

select ss.name, ss.score from student_score ss where ss.subject = '语文'
select ss.name, ss.score from student_score ss where ss.subject = '数学'
select ss.name, ss.score from student_score ss where ss.subject = '英语'

select t.name, sum(语文), sum(数学), sum(英语) from
(
select ss.name, ss.score 语文, 0 数学, 0 英语 from student_score ss where ss.subject = '语文'
union all
select ss.name, 0 语文, ss.score 数学, 0 英语 from student_score ss where ss.subject = '数学'
union all
select ss.name, 0 语文, 0 数学, ss.score 英语 from student_score ss where ss.subject = '英语'
) t
group by t.name

---

微信中搜索 “码农UP2U” 关注我的公众号吧！！！