SELECT s.sname
FROM s
WHERE 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.sname
FROM s
WHERE 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
--1
with 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

--2
with 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 code

With 
s As
(select '1' sid, 'A' sname From dual
Union All  select '2' sid, 'B' sname From dual
Union All  select '3', 'C' From dual
Union All  select '4', 'D' From dual
),sc As
(Select '1' sid, 'A' tcl, 90 scr From dual
Union All Select '1', 'B', 91 From dual
Union All Select '2', 'B', 91 From dual
Union All Select '2', 'A', 90 From dual
Union All Select '3', 'A', 91 From dual
Union All Select '3', 'B', 51 From dual
Union 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 = 1
And t1.sid = s.sid

------解决方案--------------------