3,491
社区成员
发帖
与我相关
我的任务
分享
SQL> ed
已写入 file afiedt.buf
1 with student as(
2 select 10001 studentid,'张三' studentname,1 sex from dual union all
3 select 10002,'李梅',0 from dual union all
4 select 10003,'王五',1 from dual),
5 project as(
6 select 100001 projectid,'语文' projectname,2010 year from dual union all
7 select 100002,'数学',2010 from dual union all
8 select 100003,'英语',2010 from dual union all
9 select 100004,'历史',2009 from dual union all
10 select 100005,'地理',2009 from dual),
11 stuscore as(
12 select 10001 studentid,100001 projectid,85 score from dual union all
13 select 10001,100002, 90 from dual union all
14 select 10001,100005, 80 from dual union all
15 select 10002 ,100001, 78 from dual union all
16 select 10002, 100002, 82 from dual union all
17 select 10002, 100003, 81 from dual union all
18 select 10002, 100004, 78 from dual union all
19 select 10002, 100005, 88 from dual union all
20 select 10003, 100001, 88 from dual union all
21 select 10003, 100004, 88 from dual union all
22 select 10003, 100005 ,82 from dual), --以上为提供数据的语句
23 stutable as(
24 select s.studentid,studentname,sex,
25 p.projectid,projectname,year,
26 sc.score
27 from stuscore sc,student s,project p
28 where s.studentid=sc.studentid and p.projectid=sc.projectid(+))--相当于一个视图
29 select studentname,year,max(decode(projectid,100001,projectname,null)) projectname,
30 max(decode(projectid,100001,score,null)) score,
31 max(decode(projectid,100002,projectname,null)) projectname,
32 max(decode(projectid,100002,score,null)) score,
33 max(decode(projectid,100003,projectname,null)) projectname,
34 max(decode(projectid,100003,score,null)) score,
35 max(decode(projectid,100004,projectname,null)) projectname,
36 max(decode(projectid,100004,score,null)) score,
37 max(decode(projectid,100005,projectname,null)) projectname,
38 max(decode(projectid,100005,score,null)) score
39 from stutable
40 group by studentname,year
41* order by year desc,studentname
SQL> /
STUD YEAR PROJ SCORE PROJ SCORE PROJ SCORE PROJ SCORE PROJ SCORE
---- ---------- ---- ----- ---- ----- ---- ----- ---- ----- ---- -----
李梅 2010 语文 78 数学 82 英语 81
王五 2010 语文 88
张三 2010 语文 85 数学 90
李梅 2009 历史 78 地理 88
王五 2009 历史 88 地理 82
张三 2009 地理 80
已选择6行。