来看看大家如何做这道SQL题

来看看大家怎么做这道SQL题不小心到DB2版找到的这道题：阿里baba的面试题有三个表学生表 SSID SNAME教师课

来看看大家怎么做这道SQL题
不小心到DB2版找到的这道题：

阿里baba的面试题

有三个表
学生表 S
SID SNAME
教师课表 T
TID TNAME TCL
成绩表 SC
SID TCL SCR
各字段的含义不用我标明了吧，大侠哥哥么！呵呵

现在要求写SQL查询
1、选修了A、B课程，并且A课程的成绩大于B成绩的学生姓名？

[解决办法]
SELECT SNAME
FROM S, SC T1, SC T2
WHERE S.SID = T1.SID
AND S.SID = T2.SID
AND T1.TCL = 'A'
AND T2.TCL = 'B'
AND T1.SCR > T2.SCR
[解决办法]

SQL code

SELECT s.snameFROM sWHERE EXISTS (SELECT sc1.sid, sc2.sid           FROM sc sc1 INNSER JOIN sc sc2 ON sc1.sid=sc2.sid AND sc1.tcl='A' AND sc2.tcl='B' AND sc1.scr>sc2.scr           WHERE sc1.sid=s.sid );
[解决办法]
SQL code-- inner写错了！SELECT s.snameFROM sWHERE EXISTS (SELECT sc1.sid, sc2.sid           FROM sc sc1 INNER JOIN sc sc2 ON sc1.sid=sc2.sid AND sc1.tcl='A' AND sc2.tcl='B' AND sc1.scr>sc2.scr           WHERE sc1.sid=s.sid );
[解决办法]
-- 功力不怎么样：不知道写对了没有？
-- 写错了，就当是我瞎写的！
[解决办法]
SQL code--1with s as(  select '001' sid,'张三' sname from dual union all    select '002' sid,'李四' sname from dual union all  select '003' sid,'王五' sname from dual), sc as (  select '001' sid,'A' tcl, 80 scr from dual union all  select '001' sid,'B' tcl, 70 scr from dual union all  select '002' sid,'A' tcl, 60 scr from dual union all  select '002' sid,'B' tcl, 80 scr from dual union all  select '003' sid,'A' tcl, 80 scr from dual union all  select '003' sid,'C' tcl, 80 scr from dual)      SELECT s.*    FROM s,         (SELECT a.sid            FROM (SELECT * FROM sc WHERE tcl = 'A') a, (SELECT * FROM sc WHERE tcl = 'B') b           WHERE a.sid = b.sid AND                 a.scr > b.scr) t   WHERE s.sid = t.sid--2with s as(  select '001' sid,'张三' sname from dual union all    select '002' sid,'李四' sname from dual union all  select '003' sid,'王五' sname from dual), sc as (  select '001' sid,'A' tcl, 80 scr from dual union all  select '001' sid,'B' tcl, 70 scr from dual union all  select '002' sid,'A' tcl, 60 scr from dual union all  select '002' sid,'B' tcl, 80 scr from dual union all  select '003' sid,'A' tcl, 80 scr from dual union all  select '003' sid,'C' tcl, 80 scr from dual)SELECT *  FROM s WHERE sid IN (SELECT sid                 FROM sc a                WHERE EXISTS (SELECT 1                         FROM sc b                        WHERE b.sid = a.sid AND                              a.tcl = 'A' AND                              b.tcl = 'B' AND                              a.scr > b.scr))
[解决办法]
貌似教师课表没啥用。。
[解决办法]
SQL codeWith s As(select '1' sid, 'A' sname From dualUnion All  select '2' sid, 'B' sname From dualUnion All  select '3', 'C' From dualUnion All  select '4', 'D' From dual),sc As(Select '1' sid, 'A' tcl, 90 scr From dualUnion All Select '1', 'B', 91 From dualUnion All Select '2', 'B', 91 From dualUnion All Select '2', 'A', 90 From dualUnion All Select '3', 'A', 91 From dualUnion All Select '3', 'B', 51 From dualUnion All Select '4', 'A', 61 From dual)select t1.sid,s.sname From (Select sid,scr, sign(scr - nvl(lead(scr)over(Partition By sid Order By tcl),scr)) si From sc) t1, s Where t1.si = 1And t1.sid = s.sid
------解决方案--------------------  
SQL codeselect sname from s where sid in(select a.sidfrom sc a,sc bwhere a.sid=b.sid and a.tcl='A' and b.tcl='B' and a.scr>b.scr)
[解决办法]
SQL codeWith s As(select '1' sid, 'A' sname From dualUnion All  select '2' sid, 'B' sname From dualUnion All  select '3', 'C' From dualUnion All  select '4', 'D' From dual),sc As(Select '1' sid, 'A' tcl, 90 scr From dualUnion All Select '1', 'B', 91 From dualUnion All Select '2', 'B', 91 From dualUnion All Select '2', 'A', 90 From dualUnion All Select '3', 'A', 91 From dualUnion All Select '3', 'B', 51 From dualUnion All Select '4', 'A', 61 From dual)select t1.sid,s.sname From (Select sid,scr, sign(scr - nvl(lead(scr)over(Partition By sid Order By tcl),scr)) si From scWhere tcl = 'A' Or tcl = 'B') t1, s Where t1.si = 1And t1.sid = s.sid
[解决办法]
高手！！！！！！！！！！！！