select t1.lession_des,t1.stu_name as 第一名,t2.stu_name as 第二名 from



(select c.lession_des, b.stu_name 
  from test1 a, test2 b, test3 c
 where a.score in (select max(a.score) from test1 a group by a.lession_id) 
   and b.stu_id = a.stu_id
   and c.lession_id = a.lession_id
 order by c.lession_id asc) t1 ,
 
 
 ( select v.lession_des,u.stu_name 
 from test1 t,test2 u,test3 v where  u.stu_id = t.stu_id and v.lession_id = t.lession_id and t.score  in(select max(t.score)
 from test1 t, test2 u
   where u.stu_id = t.stu_id
   and t.score not in (select max(score) from test1 t  group by t.lession_id) group by t.lession_id )) t2
   
 where t2.lession_des = t1.lession_des

------解决方案--------------------
--第三题

--游标就不写了，自己研究下吧 呵呵
select 
     T_Stu_Profile.Stu_id 学号,
     T_Stu_Profile,Stu_name 姓名,
     decode(T_Stu_Profile.Lession_id,'L001',T_Score.score) 语文,
     decode(T_Stu_Profile.Lession_id,'L002',T_Score.score) 数学，
     decode(T_Stu_Profile.Lession_id,'L003',T_Score.score) 英语，
     decode(T_Stu_Profile.Lession_id,'L004',T_Score.score) 物理，
     decode(T_Stu_Profile.Lession_id,'L005',T_Score.score) 化学，
     sum(T_Score,score) 总分
from T_Score score,T_Stu_Profile pro,T_Lession les
where  
score.Stu_id = pro.Stu_id
pro.Lession_id = les.Lession_id
group by T_Stu_Profile.Stu_id，T_Stu_Profile,Stu_name

------解决方案--------------------
第一题
SQL code

SELECT B.STU_ID, A.STU_NAME,A.LESSION_DES,a.class_id from 
(SELECT STU_ID,STU_NAME,LESSION_ID,LESSION_DES,CLASS_ID FROM T_STU_PROFILE  ,T_LESSION
WHERE LESSION_ID IN (SELECT DISTINCT LESSION_ID  FROM T_SCORE)) A, T_SCORE B
WHERE A.STU_ID=B.STU_ID(+) AND A.LESSION_ID=B.LESSION_ID(+) AND B.STU_ID IS NULL

------解决方案--------------------
第一题   
SQL code
 select a.stu_id,b.lession_id from t_student a , t_lesson b
minus
select stu_id, lession_id from t_score;

------解决方案--------------------
用一个sql就能解决问题，啥使不使用游标如何解决的都是扯淡……要是我面试就直接给个思路完事
第一个，学生和科目表做笛卡尔积，然后minus成绩表；
第二个，先对成绩排序ROW_NUMBER()OVER(PARTITION BY LESSION_ID ORDER BY SCORE DESC)，然后三个表关联做行转列，docode取ROW_NUMBER是1，2，3的；
第三个，三个表关联，用decode做行转列；
第四个，在where条件里面限制高三阶段，用sum(decode(………………))行转列求出每门高三总成绩，直接在select子句里面做关联查询，用sum的结果除考试次数
架子大概就是select b.Stu_name，sum(decode(……))/(select count(*) from T_Score t1 where t1.Stu_id=b.Stu_id and t1.Lession_id=c.Lession_id and Test_Date in (高三) and score>=60),sum(……………………