1.有下表:
 create table a
 (
    id number(10),PK
    amt number(14,2),
    type number(4)
 );
 create table b
 (
    id   number(10), FK 
    name varchar2(20)
 );
表A中的字段值:
 -----------------------
 |__ID__|__amt_|__type_|
 |__1___|__20__|____2__|
 |__2___|__30__|____3__|
 |__3___|__50__|____5__|
 |__4___|__70__|____7__|
 |__5___|__53__|____53_|
表B中的字段值:
 -----------------------
 |__ID__|__name_|
 |__1___|__aaa__|
 |__2___|__bbb__|
 |__3___|__ccc__|
 |__4___|__ddd__|
 |__5___|__fff__|
 要求:连接查询出当type为2时查询amt的值与表B中对应的name值,字段重命名为(amt1),
  当type为3时、为5时，查询出type为3的amt值加上type为5的amt值与表B中对应的name值，并将字段重命名为(amt2)；
  当type为7时、为53时，查询出type为7的amt值加上type为53的amt值与表B中对应的name值，并将字段重命名为(amt3)；
(注:使用一条语句查询)

2.有下表:
 create table studentsscore(
   stu_id varchar2(50) null ,(学号)
   kc  varchar2(50) null, (课程)
   score  decimal(18,1) null (成绩)
 )
取每科成绩前两名的学生信息并按课程号和成绩排序(注:使用一条语句(要求写出三种查询方法))

3.有下表:(stu)
 -----------------------
 |__ID__|_name_|_score_|
 |__1___|__aa__|__80 __|
 |__2___|__bb__|___90__|
 |__3___|__cc__|___95__|
 |__4___|__dd__|___70__|
 |__5___|__ee__|___60_|
用一条语句输出,成绩在90分以上评为'EXCELLENT', 80-90分评为'GOOD',其他评为'SOSO'
 
以上三题是,曾经在工作遇到SQL问题,现在已经解决,想把结果与大家一起分享!如有不足之路,欢迎讨论!

答案:
1>select b.name,sum(decode(a.type,2,amt,0)) amt1,
       sum(decode(a.type,3,amt,0))+sum(decode(a.type,5,amt,0)) amt2,
       sum(decode(a.type,7,amt,0))+sum(decode(a.type,53,amt,0)) amt3
from A,B where a.id=b.id group by b.name,
2>方法一:
select kc,stu_id,score from student as t1 where (select count(*) from studnet as t2 where t1.kc=t2.kc and t1.score>t2.score)
<2 order by kc,score desc
方法二:
select a.* from student as a where a.stu_id in ( select top 2 stu_id from student where kc=a.kc order by score dese)
方法三:
select kc, max(score) from student group by kc
union
select kc,max(score) from student as a where score not in(select max(score) from student b where a.kc = b.kc group by kc)
group by kc.
 
3> select id,name,score
  case when score>90 then 'excellent'
       when score>80 and score<=90 then 'good'
       else  'soso'
  end  as 奖评
 from stu