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行。
这个就需要用我1楼给的链接中的通用过程了 select s.studentid,studentname,sex, p.projectid,projectname,year, sc.score from stuscore sc,student s,project p where s.studentid=sc.studentid and p.projectid=sc.projectid(+)) 形成一个这样类似的视图,然后调用过程
create or replace function getScore(p_stuNo in number, p_year in varchar2) return cursor type rc is ref cursor; rc_proj rc; v_sql varchar2(2000); begin v_sql := ' '; for rc in (select projectId from B b where b.year=p_year) v_sql := v_sql||'select c.projectId as "'||rc.projectId||'", c.score as "' ||rc.projectId||'_score" from C c where c.projectId='||rc.projectId ||' and c.studentNo='||p_stuNo||' union' end loop; substr(v_sql,-1,6); open rc_proj for v_sql; return rc_proj; end getScore;select distinct a.studentName,getScore(a.studentName, year) from A a 代码大致这样,没有经过测试,你试试吧
decode(selective,case1,result....)
应该可以解决问题,请给测试数据
--studentId-studentName-Sex---
10001 张三 1
10002 李梅 0
10003 王五 1
------------------------------
科目表
--projectId--projectName--year--
100001 语文 2010
100002 数学 2010
100003 英语 2010
100004 历史 2009
100005 地理 2009
---------------------------------
成绩表
--studentId--projectId--score--
10001 100001 85
10001 100002 90
10001 100005 80
10002 100001 78
10002 100002 82
10002 100003 81
10002 100004 78
10002 100005 88
10003 100001 88
10003 100004 88
10003 100005 82
------------------------------
测试数据已给出 成绩表 每个学员的成绩可能存在缺考的情况
还有就是 最后需要根据年度查询每个学员 每个科目的成绩(包括缺考)
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行。
STUD YEAR PROJ SCORE PROJ SCORE PROJ SCORE
-- ----- ---- ----- ---- ----- ---- ----- ---- ---
李梅 2010 语文 78 数学 82 英语 81
王五 2010 语文 88
张三 2010 语文 85 数学 90
--------------------------------------------------
STUD YEAR PROJ SCORE PROJ SCORE
---- ---------- ---- ----- ---- -----
李梅 2009 历史 78 地理 88
王五 2009 历史 88 地理 82
张三 2009 地理 80
---------------------------------------
每个年度的查询结果不一样,
考试科目有多有少(不固定列)
每个年度统计一次 而不是把所有年度的记录都统计到一个表
select s.studentid,studentname,sex,
p.projectid,projectname,year,
sc.score
from stuscore sc,student s,project p
where s.studentid=sc.studentid and p.projectid=sc.projectid(+))
形成一个这样类似的视图,然后调用过程
type rc is ref cursor;
rc_proj rc;
v_sql varchar2(2000);
begin
v_sql := ' ';
for rc in (select projectId from B b where b.year=p_year)
v_sql := v_sql||'select c.projectId as "'||rc.projectId||'", c.score as "' ||rc.projectId||'_score" from C c where c.projectId='||rc.projectId
||' and c.studentNo='||p_stuNo||' union'
end loop;
substr(v_sql,-1,6);
open rc_proj for v_sql;
return rc_proj;
end getScore;select distinct a.studentName,getScore(a.studentName, year)
from A a
代码大致这样,没有经过测试,你试试吧