当前位置: 代码迷 >> Oracle开发 >> 求一条SQL语句?解决思路
  详细解决方案

求一条SQL语句?解决思路

热度:77   发布时间:2016-04-24 07:23:05.0
求一条SQL语句?
--列出所有各科成绩最高的员工信息 要显示 e.eid 、e.name 、t.course、t.grade

以及怎么解析一条语句?求方法[b][/b]。

create table employee 
(
eid integer,
name varchar2(200),
department varchar2(200),
job varchar2(20),
email varchar2(200),
password varchar2(200)
)
insert into employee values(10001,'李明','SBB','EG',null,null);
insert into employee values(10003,'李四','LUCK','ITM',null,null);
insert into employee values(11045,'胡斐','SBB','EG',null,null);
insert into employee values(10044,'张三','MTD','ETN',null,null);
insert into employee values(10023,'王刚','MMM','ETN',null,null);

create table training 
(
courseid integer,
eid integer,
course varchar2(20),
grade integer,
orders varchar2(200)
)
insert into training values(1,10001,'T-SQL',60,null);
insert into training values(3,10045,'java',71,null);
insert into training values(2,10003,'oracle',59,null);
insert into training values(1,10003,'T-SQL',90,null);
insert into training values(3,10044,'java',78,null);
insert into training values(2,10001,'oracle',69,null);
insert into training values(2,10023,'oracle',70,null);
insert into training values(3,20001,'java',69,null);
insert into training values(3,10078,'java',58,null);

------解决方案--------------------
关联后排序,然后取第一条

SQL code
select e.eid,e.name,t.course,t.grade from employee  e,(select courseid,eid ,course ,grade ,rank() over(partition by courseid order by grade desc)  as rkfrom training  ) twhere e.eid=t.eidand t.rk=1
------解决方案--------------------
select x.eid,x.name,x.course,x.grade from(select e.eid,e.name,t.course,t.grade,rank() over(partition by t.course order by t.grade desc) r from employee e, training t where e.eid=t.eid)x where r=1;
关联排序,而后从结果集中取第一条
------解决方案--------------------
关联后排序,然后取第一条

SQL code
select e.eid,e.name,t.course,t.grade 
from employee e,
(select courseid,
eid ,
course ,
grade ,
rank() over(partition by courseid order by grade desc) as rk
from training ) t
where e.eid=t.eid
and t.rk=1
  相关解决方案