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