跪求 sql 查询高手,该如何处理
日期：2014-05-18 浏览次数：20634 次
跪求 sql 查询高手
create table student
(
stuNo int,
stuName char(6)
)
insert into student values(1,'李明')
insert into student values(2,'小红')
insert into student values(3,'小利')
insert into student values(4,'小甜甜')
insert into student values(5,'咪咪')
insert into student values(6,'好莱坞')
create table course
(
cNo int,--为课程号
cName char(6),--课程名
cteacher char(6)--代课老师
)
insert into course values(1,'语文','李老师')
insert into course values(2,'数学','马老师')
insert into course values(3,'英语','杜老师')
select * from course
create table elective--成绩表
(
sNo int,
cNo int,
elecGrade int
)
select * from elective where cNo=1 and cNo=2
insert into elective values(1,1,80)
insert into elective values(1,2,70)
insert into elective values(2,2,60)
insert into elective values(2,3,60)
insert into elective values(1,2,60)
insert into elective values(1,3,40)
insert into elective values(1,3,50)
select * from student
select * from course
select * from elective
-- 3． 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名???????
select student.stuName from course join student
on student.stuNo = course.cNo
where cNo = 1 or cNo= 2
-- 4． 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号????
5． 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩
??????????
------解决方案--------------------
create table student
(
stuNo int,
stuName char(6)
)
insert into student values(1,'李明')
insert into student values(2,'小红')
insert into student values(3,'小利')
insert into student values(4,'小甜甜')
insert into student values(5,'咪咪')
insert into student values(6,'好莱坞')
create table course
(
cNo int,--为课程号
cName char(6),--课程名
cteacher char(6)--代课老师
)
insert into course values(1,'语文','李老师')
insert into course values(2,'数学','马老师')
insert into course values(3,'英语','杜老师')
select * from course
create table elective--成绩表
(
sNo int,
cNo int,
elecGrade int
)
select * from elective where cNo=1 and cNo=2
insert into elective values(1,1,80)
insert into elective values(1,2,70)
insert into elective values(2,2,60)
insert into elective values(2,3,60)
insert into elective values(1,2,60)
insert into elective values(1,3,40)
insert into elective values(1,3,50)
select * from student
select * from course
select * from elective
-- 3． 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名???????
select student.stuName from course join student
on student.stuNo = course.cNo
where cNo = 1 or cNo= 2
-- 4． 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号????
5． 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩
??????????
------解决方案--------------------
- SQL code
--3． 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名
select stuname
from student s
where exists(
select 1
from elective e
where exists(
select 1
from elective
where sNo=e.sNo and cNo=1 and e.cNo=2)
and e.sNo=s.stuNo)
/*
stuname
李明
*/
------解决方案--------------------
4 5没看懂,帮顶.
------解决方案--------------------
-- 3． 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名???????
- SQL code
select stuName from student s where exists(select 1 from course where sNo=s.stuNo and cNo = 1) and exists(select 1 from course where sNo=s.stuNo and cNo = 2)
------解决方案--------------------
- SQL code
-- 3． 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名
select m.stuname from student m where stuNo in
(
select sno from
(
select distinct sno from elective where cno = 1
union all
select distinct sno from elective where cno = 1
) t
group by sno having count(1) = 2
)
/*
stuname
-------
李明
（所影响的行数为 1 行）
*/
-- 4．列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号
--你的sno为1,cno为2怎么多次？
select m.stuno
from student m, elective n1 , elective n2
where m.stuno = n1.sno and m.stuno = n2.sno and n1.cno = 1 and n2.cno = 2 and n1.elecgrade > n2.elecgrade
/*
stuno
-----------
1
1
（所影响的行数为 2 行）
*/
select distinct m.stuno
from student m, elective n1 , elective n2
where m.stuno = n1.sno and m.stuno = n2.sno and n1.cno = 1 and n2.cno = 2 and n1.elecgrade > n2.elecgrade
/*
stuno
-----------
1
（所影响的行数为 1 行）
*/
--5.列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩
--你的sno为1,cno为2怎么多次？所以出现两个结果
select m.stuno , n1.elecgrade , n2.elecgrade
from student m, elective n1 , elective n2
where m.stuno = n1.sno and m.stuno = n2.sno and n1.cno = 1 and n2.cno = 2 and n1.elecgrade > n2.elecgrade
/*
stuno elecgrade elecgrade
----------- ----------- -----------
1 80 70
1 80 60
（所影响的行数为 2 行）
*/
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
