来看看大家怎么做这道SQL题

codearts 2010-04-27 03:01:14
不小心到DB2版找到的这道题:

阿里baba的面试题

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

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

...全文
237 12 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
summer123428316 2010-04-29
  • 打赏
  • 举报
回复
我看看看,才知道自己老菜了..唉
木子0204 2010-04-27
  • 打赏
  • 举报
回复
高手!!!!!!!!!!!!
cyousor 2010-04-27
  • 打赏
  • 举报
回复

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

哦,应该加上一个条件,
心中的彩虹 2010-04-27
  • 打赏
  • 举报
回复

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)

cyousor 2010-04-27
  • 打赏
  • 举报
回复

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
jy03070017 2010-04-27
  • 打赏
  • 举报
回复
貌似教师课表没啥用。。
tangren 2010-04-27
  • 打赏
  • 举报
回复
--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))
luoyoumou 2010-04-27
  • 打赏
  • 举报
回复
-- 功力不怎么样:不知道写对了没有?
-- 写错了,就当是我瞎写的!
luoyoumou 2010-04-27
  • 打赏
  • 举报
回复
-- 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 );
luoyoumou 2010-04-27
  • 打赏
  • 举报
回复
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 );
huxingz 2010-04-27
  • 打赏
  • 举报
回复
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
codearts 2010-04-27
  • 打赏
  • 举报
回复
写一下这道题,可以看出一个人的SQL功力。

猩猩们都来写一下

17,140

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧