2021-05-19 20:08

# 查询同时选修了c01课程和c02课程的成绩

select cno,degree from sc where sno in(select sno from sc where cno='c01' and sno in (select sno from sc where cno='c02')) 我这个只查询了c01和c02但是结果为什么会有c03的

go
create table student
(sno char(10) not null primary key,
sname varchar(8) not null,
ssex char(2) constraint con_ssex check(ssex='男'or ssex='女'),
sbirthday datetime,
sdep char(16),
speciality varchar(20)
);

create table course
(cno char(5) not null primary key,
cname varchar(20) not null,
cpno char(5),
ccredit tinyint,
FOREIGN KEY(cpno) REFERENCES course(cno)
);

create table sc
(sno char(10) not null,
cno char(5) not null,
degree    decimal(5,1) constraint con_degree check(degree between 0 and 100),
primary key(sno,cno),
FOREIGN KEY(sno) REFERENCES student(sno),
FOREIGN KEY(cno) REFERENCES course(cno)
);

create table teacher
(tno char(3) not null primary key,
tname varchar(8) not null,
tsex char(2) constraint con_tsex check(tsex='男' or tsex='女'),
tbirthday datetime,
tdept char(16)
);

create table teaching
(cno char(5) not null,
tno char(3) not null,
cterm tinyint,
primary key(tno,cno),
FOREIGN KEY(cno) REFERENCES course(cno),
FOREIGN KEY(tno) REFERENCES teacher(tno)
);

insert
into student(sno,sname,ssex,sbirthday,sdep,speciality)
values ('20050101','李勇','男','2001-01-12','cs','计算机应用'),
('20050201','刘晨','女','2002-06-04','is','电子商务'),
('20050301','王敏','女','2003-12-23','ma','数学'),
('20050202','张立','男','2003-08-25','is','电子商务');

insert
into course(cno,cname,cpno,ccredit)
values ('C01','数据库',NULL,'4'),
('C02','数学',NULL,'3'),
('C03','信息系统','C01','4'),
('C04','操作系统','C03','3');

insert
into sc(sno,cno,degree)
values ('20050101','C01','92'),
('20050101','C02','85'),
('20050101','C03','88'),
('20050201','C02','90'),
('20050201','C03','80');

insert
into teacher(tno,tname,tsex,tbirthday,tdept)
values ('101','李新','男','1977-01-12','cs'),
('102','钱军','女','1968-06-04','cs'),
('201','王小花','女','1979-12-23','is'),
('202','张小青','男','1968-08-25','is');

insert
into teaching(cno,tno,cterm)
values ('C01','101','2'),
('C02','102','1'),
('C03','201','3'),
('C04','202','4');

• 点赞
• 写回答
• 关注问题
• 收藏
• 邀请回答

#### 13条回答默认 最新

• CSDN专家-sinjack 2021-05-19 20:21
已采纳

如果需求是查询  ，只选修了c01课程和c02课程的，那就不是这么写了。

点赞 评论
• CSDN专家-sinjack 2021-05-19 20:33
``````select cno,degree from sc where sno in(select sno from sc where cno='c01' and sno in (select sno from sc where cno='c02')) and sno in(
select sno from sc group by sno having count(sno)=2)``````
点赞 1 评论
• CSDN专家-sinjack 2021-05-19 20:38

如果你满意我的回答，请点采纳。你的认可是我们互助的动力。

点赞 1 评论
• 你这个括号位置不对吧，改成这样试一下

``select cno,degree from sc where sno in(select sno from sc where cno='c01') and sno in (select sno from sc where cno='c02')``
点赞 评论
• CSDN专家-sinjack 2021-05-19 20:16

你查询的是同时选修了c01,c02课程的学生，另外同时选修了其他课程的也满足条件啊。

点赞 评论
• 查询语句有问题，把表结构发出来看看。

点赞 评论
• CSDN专家-sinjack 2021-05-19 20:19

你写的sql没问题的

点赞 评论
• CSDN专家-Time 2021-05-19 20:33

因为你的查询语句 是先查sno 然后这个sno里有个 人呢 好死不死的 选了3门课 那肯定能查到 cno3啊

select cno,degree from sc where sno in（这里是一个集合，包含了20050101）

铁能查到cno3啊

点赞 评论
• CSDN专家-Time 2021-05-19 20:38

如果你只想查cno2和 cno1

select cno,degree,sno from sc where cno in ('cno1','cno2') left join student on sc.sno = student.sno;

点赞 评论
• 照-辉 2021-05-19 20:10

可以请你截一下mysql的图片吗，还有你的表

点赞 评论
• ?D?.L 2021-05-19 20:22

点赞 评论
• 有问必答小助手 2021-05-20 11:31

您好，我是有问必答小助手，您的问题已经有小伙伴解答了，您看下是否解决，可以追评进行沟通哦~

如果有您比较满意的答案 / 帮您提供解决思路的答案，可以点击【采纳】按钮，给回答的小伙伴一些鼓励哦～～

点赞 评论
• xyxyzz 2021-05-21 12:03

select sno, cno,DEGREE from sc where cno in('c02','co1') group by sno,cno,DEGREE having COUNT( distinct cno)>2

点赞 评论