求帮忙写一条简单的sql查询语句

[diyinyu0476]

create table SC ( -学生选课表
    sno varchar(5),  --学生编号
    cno varchar(5),  --课程号
    grade number  --成绩
);

现在要查找出选修了课程'1'和课程'2‘，且 课程1的成绩>课程2成绩 的学生编号
刚学sql，求指点，谢谢！

[壹四]

1L大侠说得对

[sxq129601]

select t1.sno from (select sno, max(grade) grade from sc where cno = '课程1' group by sno) t1, (select sno, max(grade) grade from sc where cno = '课程2' group by sno) t2, (select distinct sno from sc where cno in ('课程1', '课程2')) t3 where t3.sno = t1.sno(+) and t3.sno = t2.sno(+) and t1.sno is not null and t2.sno is not null and t1.grade > t2.grade

[qq_38884602]

select * from (select sno,score from sc where cno='c001') t1,( select sno,score from sc where cno='c002') t2 where t1.sno=t2.sno and t1.score>t2.score

[jdsnhan]

select sno from (
select  max(decode(cno,'课程1',grade,0)) 课程1成绩,
             max(decode(cno,'课程2',grade,0)) 课程2成绩,
             sno
from sc
group by sno)
where  课程1成绩 >  课程2成绩
and  课程1成绩 != 0 and  课程2成绩 !=0 

[雨巷啊]

WITH XS AS
 (
  
  SELECT SNO, SC1.GRADE KC1, SC2.GRADE KC2
    FROM SC SC1, SC SC2
   WHERE SC1.SNO = SC2.SNO
     AND SC1.CNO = (SELECT cno FROM 课程表 WHERE kcm = '课程1')
     AND SC2.CNO = (SELECT cno FROM 课程表 WHERE kcm = '课程2')
  
  )
SELECT DISTINCT (SNO) FROM XS WHERE XS.KC1 > XS.KC2

[ACE-2017]

select a.* from (select * from sc a where a.cno='课程一') a,(select * from sc b where b.cno='课程二) b where a.sno！=b.sno and a.score>b.score

[tiegenZ]

select (case when A.kc1>A.kc2 then A.snc else ' ' end) as 学生编号 --课程1的成绩>课程2成绩 的学生编号 from( select sno, --学生编号 (case when cno='课程1' then grade else 0 end) as kc1, --学生课程1成绩 (case when cno='课程2' then grade else 0 end) as kc2 ---学生课程2成绩 from SC where nvl((case when cno='课程1' then grade else 0 end),0)>0 and nvl((case when cno='课程2' then grade else 0 end),0)>0 )A ---------------------------------------------- 解题思路：把课程1与课程2成绩>0的学生课程1与课程2的成绩分别查出来 再将课程1与课程2的成绩去做对比 如果 课程1的成绩>课程2成绩则将学生编号展示出来 否则不显示 （课程1与课程2成绩都>0则证明该同时选修了课程'1'和课程'2‘）