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
