oracle行转列练习
1 ----------------------第一题--------------------------- 2 create table STUDENT_SCORE 3 ( 4 name VARCHAR2(20), 5 subject VARCHAR2(20), 6 score NUMBER(4,1) 7 ) 8 9 insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '语文', 78.0); 10 insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '数学', 88.0); 11 insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '英语', 98.0); 12 insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '语文', 89.0); 13 insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '数学', 76.0); 14 insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '英语', 90.0); 15 insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '语文', 99.0); 16 insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '数学', 66.0); 17 insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '英语', 91.0); 18 19 --emp 20 /* 21 显示格式: 22 语文 数学 英语 23 及格 优秀 不及格 24 */ 25 --方法一 26 select name, 27 (select score from student_score s1 where subject = '语文' and s1.name=s.name) 语文, 28 (select score from student_score s1 where subject = '数学' and s1.name=s.name) 数学, 29 (select score from student_score s1 where subject = '英语' and s1.name=s.name) 英语 30 from student_score s group by name 31 32 33 34 --方法二 decode 35 select s.name, 36 sum(decode(subject, '语文',s.score,0)) 语文, 37 sum(decode(subject, '数学',s.score,0)) 数学, 38 sum(decode(subject, '英语',s.score,0)) 英语 39 from student_score s 40 group by s.name 41 --方法三 case when 42 select s.name , sum(case s.subject when '语文' then s.score else 0 end) "语文", 43 sum(case s.subject when '数学' then s.score else 0 end) 数学, 44 sum(case s.subject when '英语' then s.score else 0 end) 英语 45 from student_score s group by s.name 46 --方法四 47 采用 join表连接的方式 48 49 50 51 --判断及格否 52 select t.name 名字, 53 case 54 when t.y between 90 and 100 then 55 '优秀' 56 when t.y between 60 and 90 then 57 '及格' 58 when t.y between 0 and 60 then 59 '不及格' 60 end 语文, 61 case 62 when t.s between 90 and 100 then 63 '优秀' 64 when t.s between 60 and 90 then 65 '及格' 66 when t.s between 0 and 60 then 67 '不及格' 68 end 数学, 69 case 70 when t.e between 90 and 100 then 71 '优秀' 72 when t.e between 60 and 90 then 73 '及格' 74 when t.e between 0 and 60 then 75 '不及格' 76 end 英语 77 78 from ( 79 80 select s.name, 81 sum(decode(subject, '语文', s.score, 0)) y, 82 sum(decode(subject, '数学', s.score, 0)) s, 83 sum(decode(subject, '英语', s.score, 0)) e 84 from student_score s 85 group by s.name) t 86 87 88 89 -----------------------第二题-------------------------------- 90 91 create table test( 92 id number(10) primary key, 93 type number(10) , 94 t_id number(10), 95 value varchar2(5) 96 ); 97 insert into test values(100,1,1,'张三'); 98 insert into test values(200,2,1,'男'); 99 insert into test values(300,3,1,'50'); 100 /* 101 姓名 性别 年龄 102 --------- -------- ---- 103 张三 男 50 104 */ 105 106 107 108 --方法一 109 --1 110 select listagg(decode(t.type, 1, t.value)) within group(order by value) 姓名, 111 listagg(decode(t.type, 2, t.value)) within group(order by value) 性别, 112 listagg(decode(t.type, 3, t.value)) within group(order by value) 年龄 113 from test t 114 group by t.t_id 115 116 --方法二 117 select max(decode(t.type, 1, t.value)) 姓名, 118 max(decode(t.type, 2, t.value)) 性别, 119 max(decode(t.type, 3, t.value))年龄 120 from test t group by t.t_id 121 122 123 --方法三表连接方式 124 select * from test 125 126 select * 127 from (select value name,t_id from test where type = 1) m1 128 join (select value sex,t_id from test where type = 2) m2 129 on m1.t_id = m2.t_id 130 131 -------------------------第三题------------------------- 132 133 create table tmp(rq varchar2(10),shengfu varchar2(5)) 134 135 insert into tmp values('2005-05-09','胜'); 136 insert into tmp values('2005-05-09','胜'); 137 insert into tmp values('2005-05-09','负'); 138 insert into tmp values('2005-05-09','负'); 139 insert into tmp values('2005-05-10','胜'); 140 insert into tmp values('2005-05-10','负'); 141 insert into tmp values('2005-05-10','负'); 142 143 select * from tmp; 144 胜 负 145 2005-05-09 2 2 146 2005-05-10 1 2 147 148 149 --方法一 150 select rq, 151 sum(decode(shengfu, '胜', 1, '负', 0)) 胜, 152 sum(decode(shengfu, '胜', 0, '负', 1)) 负 153 from tmp 154 group by rq 155 156 157 158