weixin_46186490
?D?.L
2021-05-19 20:08
采纳率: 100%
浏览 101

查询同时选修了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的

 

以下是题目数据

create database grademanager
use grademanager
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条回答 默认 最新

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

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

    点赞 评论
  • technologist_36
    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 评论
  • technologist_36
    CSDN专家-sinjack 2021-05-19 20:38

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

    点赞 1 评论
  • technologist_41

    你这个括号位置不对吧,改成这样试一下

    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')
    点赞 评论
  • technologist_36
    CSDN专家-sinjack 2021-05-19 20:16

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

    点赞 评论
  • software7503

    查询语句有问题,把表结构发出来看看。

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

    你写的sql没问题的

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

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

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

    铁能查到cno3啊

    点赞 评论
  • technologist_32
    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;

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

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

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

     

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

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

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

    ps:问答VIP仅需29元,即可享受5次/月 有问必答服务,了解详情>>>https://vip.csdn.net/askvip?utm_source=1146287632

    点赞 评论
  • xyxyzz
    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

    点赞 评论

相关推荐