--列出所有各科成绩最高的员工信息 要显示 e.eid 、e.name 、t.course、t.grade 以及怎么解析一条语句?求方法。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);
(
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);
解决方案 »
- Oracle存储过程 在PL/SQL中如何给%rowtype传参?
- pls-00307:有太多的“TYP_LD”声明与此次调用相匹配
- 相信高手们都已解决这个问题,请帮小弟一把
- oracle自建job时间自动变更
- 在Oracle如何用SQL语句把指定行号之间的记录查询出来
- DataGuard搭建问题。求助
- 关于数据导入的问题,高手指点一下,高分求救??
- OracleOraHome81ManagementServer服务启动不了,请帮忙解决!
- 怎么设置blob字段的存储的大小???????????????
- 送分了
- 用OLE DB的列绑定的方式如何插入记录
- oracle 存储过程 临时表for 循环 添加数据
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
关联排序,而后从结果集中取第一条
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
其实里面是用到一个循环去比较而已
-------------------------------------------------------------------
select tx2.course, tx2.grade, tx1.eid, tx1.name
from employee tx1,
(
select t.*,
(
select count(distinct grade) from training t1
where t1.grade>=t.grade
and t1.courseid=t.courseid
) rn
from training t
) tx2
where tx1.eid=tx2.eid and tx2.rn=1
order by tx2.courseid;