17,075
社区成员
发帖
与我相关
我的任务
分享
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
Where tcl = 'A' Or tcl = 'B') t1, s
Where t1.si = 1
And t1.sid = s.sid
select sname from s where sid in(select a.sid
from sc a,sc b
where a.sid=b.sid and a.tcl='A' and b.tcl='B' and a.scr>b.scr)
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
--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))
-- 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 );
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 );