有个SQL查询语句请教大家： 选出 “语文”和“数学”都选修并且语文成绩比数学成绩高的所有学生的姓名

Question

数据表：学生表student表 sid sname
教师表 teacher表 tid tname
课程表 course表 cid cname tid
成绩表sc 表 sid cid score
希望 写全语句 谁帮忙给完整的写成一个语句啊，对了，必加分

Answer 1

student:s
course:c
teacher:t
sc:sc1,sc2
先分别选语文的和数学的选修学生和分数
1、create view yuwen as
select sid,score from sc1,c where c.cid=sc1.cid and c.cname=‘语文'
2、create view shuxue as
select sid,score from sc2,c where c.cid=sc2.cid and c.cname=‘数学'
然后选出以上数据中同时选了两门课程且语文比数学成绩高的学生
3、create view student_id as
select yuwen.sid from yuwen,shuxue where yuwen.score>shuxue.score and yuwen.sid=shuxue.sid
选出语文成绩比数学成绩高的学生的sid:最后的结果语句就有了：
4、select s.sname from s,student_id where s.sid = student_id
也可以这样写：
1、create view yuwen as
select sname,sc1.sid,score from s,sc1,c where s.sid =sc1.sid and c.cid=sc1.cid and c.cname=‘语文'
2、create view shuxue as
select sname, sc2.sid,score from sc2,c where c.cid=sc2.cid and c.cname=‘数学'
3、select sname from (select yuwen.sname from yuwen,shuxue where yuwen.score>shuxue.score and yuwen.sid=shuxue.sid)
以上是一个详细的分析过程。写在一个句子里的话句子结构就有点复杂了。希望网友们补齐啊。追问

谢谢 你的分析 但是 我希望把这个过程写在一个 语句里，我使用表相关联 就出很大问题了

Answer 2

select db.sname
from
(select a.sname,c.cname,b.score
from student as a
inner join sc as b
on a.sid=b.sid
inner join course as c
on b.cid=c.cid and c.cname='语文') as db --姓名、科目（语文）、成绩
inner join (select a.sname,c.cname,b.score
from student as a
inner join sc as b
on a.sid=b.sid
inner join course as c
on b.cid=c.cid and c.cname='数学') as db2 --姓名、科目（数学）、成绩
on db.sname=db2.sname
where db.score>db2.score --语文成绩大于数学成绩

Answer 3

select * from
(
select
s.sid,
s.sname,
a. score as 语文成绩,
b. score as 数学成绩
from
student s
left join
sc a on s.sid=a.sid
left join
sc b on s.sid=b.sid
left join course c
on a.cid=c.cid
left join course d
on b.cid=d.cid
where
c.cname='语文' and d.cname='数学'
)tb
where
tb.语文成绩 is not null and
tb.数学成绩 is not null and
tb.语文成绩>tb.数学成绩

Answer 4

我这个就是一个语句啊，不可以用吗？
select distinct stu.sname as Name,
max(case when cou.cname='yuwen' then sc.score else 0 end) as yuwen,
max(case when cou.cname='shuxue' then sc.score else 0 end) as shuxue
--other courses if you needed
from score sc

left join student stu on stu.sid=sc.sid
left join course cou on cou.cid=sc.cid
group by sname
having yuwen>shuxue追问

可能你没看清问题，条件是都选秀且语文成绩比数学成绩高的学生姓名

Answer 5

你的课程表是不是漏一个字段追问

没有少 一个 课程号 一个课程名字 一个 教师号 不需要别的了