事件描述：公司人力资源系统的数据库记录了每位员工的培训信息，有如下3张表：
STAFF (Sno,Sname,Sdept,Sage) Sno,Sname,Sdept,Sage 分别代表学号、学员姓名、所属单位、学员年龄
COURSE (Cno,Cname ) Cno,Cname 分别代表课程编号、课程名称
SCRELATION ( Sno,Cno,Grade ) Sno,Cno,Grade 分别代表学号、所选修的课程编号、学习成绩
查询每个单位所选修课程的最高分和最低分的学员学号、姓名、所属单位、学习成绩，并增加最高（低）分说明列。
这个如何解决呢？主要是如何增加说明列！！求各位大哥指教！！！麻烦给出详细脚本谢谢..！！
------解决方案--------------------
select *
from (select mingrade.sdept,
mingrade.cname,
mingrade.ming 最低分,
grade.sno,
grade.sname
from (select STAFF.Sdept,
COURSE.CNAME,
min(to_number(SCRELATION.Grade)) as ming
from STAFF, COURSE, SCRELATION
where STAFF.Sno = SCRELATION.Sno
and SCRELATION.Cno = COURSE.Cno
group by STAFF.Sdept, COURSE.CNAME
order by STAFF.Sdept) mingrade,
(select STAFF.Sno,
STAFF.Sname,
COURSE.Cname,
SCRELATION.Grade,
STAFF.Sdept
from STAFF, COURSE, SCRELATION
where STAFF.Sno = SCRELATION.Sno
and SCRELATION.Cno = COURSE.Cno) grade
where mingrade.cname = grade.cname
and mingrade.Sdept = grade.sdept
and mingrade.ming = grade.grade
order by mingrade.sdept) zxcj,
(select maxgrade.sdept,
maxgrade.cname,
maxgrade.maxg 最高分,
grade.sno,
grade.sname
from (select STAFF.Sdept,
COURSE.CNAME,
max(to_number(SCRELATION.Grade)) as maxg
from STAFF, COURSE, SCRELATION
where STAFF.Sno = SCRELATION.Sno
and SCRELATION.Cno = COURSE.Cno
group by STAFF.Sdept, COURSE.CNAME
order by STAFF.Sdept) maxgrade,
(select STAFF.Sno,
STAFF.Sname,
COURSE.Cname,
SCRELATION.Grade,
STAFF.Sdept
from STAFF, COURSE, SCRELATION
where STAFF.Sno = SCRELATION.Sno
and SCRELATION.Cno = COURSE.Cno) grade
where maxgrade.cname = grade.cname
and maxgrade.Sdept = grade.sdept
and maxgrade.maxg = grade.grade
order by maxgrade.sdept) zdcj
where zxcj.sdept = zdcj.sdept
and zxcj.cname = zdcj.cname
------解决方案--------------------
- SQL code
SELECT SNO, SNAME, SDEPT, GRADE, DECODE(GRADE, MAX_, '最高分', MIN_, '最低分', '程序错误') FROM SELECT T1.SNO, T1.SNAME, T1.SDEPT, T3.GRADE, MAX(T3.GRADE) OVER(PARTITION BY T1.SDEPT) MAX_, MIN(T3.GRADE) OVER(PARTITION BY T1.SDEPT) MIN_ FROM STAFF T1, COURSE T2, SCRELATION T3 WHERE T3.SNO = T1.SNO AND T3.CNO = T2.CNO) WHERE (GRADE = MAX_ OR GREAD = MIN_)
------解决方案--------------------
- SQL code
顶上
------解决方案--------------------
- SQL code
select * from (select mingrade.sdept, mingrade.cname, mingrade.ming 最低分, grade.sno, grade.sname from (select STAFF.Sdept, COURSE.CNAME, min(to_number(SCRELATION.Grade)) as ming from STAFF, COURSE, SCRELATION where STAFF.Sno = SCRELATION.Sno and SCRELATION.Cno = COURSE.Cno group by STAFF.Sdept, COURSE.CNAME order by STAFF.Sdept) mingrade, (select STAFF.Sno, STAFF.Sname, COURSE.Cname, SCRELATION.Grade, STAFF.Sdept from STAFF, COURSE, SCRELATION where STAFF.Sno = SCRELATION.Sno and SCRELATION.Cno = COURSE.Cno) grade where mingrade.cname = grade.cname and mingrade.Sdept = grade.sdept and mingrade.ming = grade.grade order by mingrade.sdept) zxcj, (select maxgrade.sdept, maxgrade.cname, maxgrade.maxg 最高分, grade.sno, grade.sname from (select STAFF.Sdept, COURSE.CNAME, max(to_number(SCRELATION.Grade)) as maxg from STAFF, COURSE, SCRELATION where STAFF.Sno = SCRELATION.Sno and SCRELATION.Cno = COURSE.Cno group by STAFF.Sdept, COURSE.CNAME order by STAFF.Sdept) maxgrade, (select STAFF.Sno, STAFF.Sname, COURSE.Cname, SCRELATION.Grade, STAFF.Sdept from STAFF, COURSE, SCRELATION where STAFF.Sno = SCRELATION.Sno and SCRELATION.Cno = COURSE.Cno) grade where maxgrade.cname = grade.cname and maxgrade.Sdept = grade.sdept and maxgrade.maxg = grade.grade order by maxgrade.sdept) zdcj where zxcj.sdept = zdcj.sdept and zxcj.cname = zdcj.cname