有学生选课关系
S(Snum,Sname,Ssex,Sage,Dnum)
Snum Sname Sex Age SD
S001 王明 男 19 D2
S002 李勇 男 23 D3
S003 刘燕 女 21 D1
S004 王萍 女 23 D1
S005 王佳 男 24 D3
S006 赵婷 女 20 D1
SC(Snum,Cnum,Score)
Snum Cnum Score
S001 C1 83
S001 C2 89
S001 C3 65
S001 C4 85
S001 C5 85
S002 C3 69
S002 C4 75
S003 C1 95
S004 C1 85
S005 C2 92
S005 C3 76
C(Cnum,Cname,Cfreq)
Cnum Cname Cfreq
C1 数据库系统原理 4
C2 C程序设计 4
C3 计算机体系结构 3
C4 自动控制原理 2
C5 数据结构 4
求一个简单的sql语句,不希望是我下面写出来的那句:查询选修全部课程的学生姓名 ,后来我在网上查到一个
select Sname
from S
where not exists
(select *
from C
where not exists
(select *
from SC
where Snum=S.Snum
and Cnum=C.Cnum))
选出来的结果是对的:是王明,
但我去掉两个not,应该是负负得正,结果是一样的,可是:
即:select Sname
from S
Where exists
(select *
from C
where exists
(select *
from SC
where Snum=S.Snum
and Cnum=C.Cnum)) 结果是:王明,李勇,刘燕, 王萍 ,王佳 ,由结果看来,不是选修了所有课的学生而是选修了课的学生,为什么是这样呢,?exists是怎么理解的啊?
S(Snum,Sname,Ssex,Sage,Dnum)
Snum Sname Sex Age SD
S001 王明 男 19 D2
S002 李勇 男 23 D3
S003 刘燕 女 21 D1
S004 王萍 女 23 D1
S005 王佳 男 24 D3
S006 赵婷 女 20 D1
SC(Snum,Cnum,Score)
Snum Cnum Score
S001 C1 83
S001 C2 89
S001 C3 65
S001 C4 85
S001 C5 85
S002 C3 69
S002 C4 75
S003 C1 95
S004 C1 85
S005 C2 92
S005 C3 76
C(Cnum,Cname,Cfreq)
Cnum Cname Cfreq
C1 数据库系统原理 4
C2 C程序设计 4
C3 计算机体系结构 3
C4 自动控制原理 2
C5 数据结构 4
求一个简单的sql语句,不希望是我下面写出来的那句:查询选修全部课程的学生姓名 ,后来我在网上查到一个
select Sname
from S
where not exists
(select *
from C
where not exists
(select *
from SC
where Snum=S.Snum
and Cnum=C.Cnum))
选出来的结果是对的:是王明,
但我去掉两个not,应该是负负得正,结果是一样的,可是:
即:select Sname
from S
Where exists
(select *
from C
where exists
(select *
from SC
where Snum=S.Snum
and Cnum=C.Cnum)) 结果是:王明,李勇,刘燕, 王萍 ,王佳 ,由结果看来,不是选修了所有课的学生而是选修了课的学生,为什么是这样呢,?exists是怎么理解的啊?
DROP TABLE s;
DROP TABLE sc;
DROP TABLE c;
create table S(snum varchar2(10),sname varchar2(10),ssex varchar2(10),sage int,sDnum varchar2(10));
create table SC(Snum varchar2(10),Cnum varchar2(10),Score int);
create table C(Cnum varchar2(10),Cname varchar2(20),Cfreq int);
insert into s values('S001','王明','男', 19 ,'D2');
insert into s values('S002','李勇','男', 23 ,'D3');
insert into s values('S003','刘燕','女', 21 ,'D1');
insert into s values('S004','王萍','女', 23 ,'D1');
insert into s values('S005','王佳','男', 24 ,'D3');
insert into s values('S006','赵婷','女', 20 ,'D1');
insert into sc values('S001','C1',83);
insert into sc values('S001','C2',89);
insert into sc values('S001','C3',65);
insert into sc values('S001','C4',85);
insert into sc values('S001','C5',85);
insert into sc values('S002','C3',69);
insert into sc values('S002','C4',75);
insert into sc values('S003','C1',95);
insert into sc values('S004','C1',85);
insert into sc values('S005','C2',92);
insert into sc values('S005','C3',76);
insert into c values('C1','数据库系统原理',4);
insert into c values('C2','C程序设计',4);
insert into c values('C3','计算机体系结构',3);
insert into c values('C4','自动控制原理',2);
insert into c values('C5','数据结构',4);
SELECT *
FROM S
WHERE SNUM IN
(SELECT SNUM
FROM (SELECT SC.SNUM, COUNT(SC.SNUM) CC FROM SC GROUP BY SC.SNUM)
WHERE CC = (SELECT COUNT(1) FROM C));
输出:
S001 王明 男 19 D2
SC(Snum,Cnum,Score)
Snum Cnum Score
S001 C2 89
S001 C3 65
S002 C3 69
S002 C4 75
S003 C1 95
S004 C1 85
S005 C2 92
S005 C3 76
想把重复了Snum的记录选出来,有
S001 C2 89
S001 C3 65
S002 C3 69
S002 C4 75
S005 C2 92
S005 C3 76
sql怎么写啊
select Snum,Cnum,Score from SC where Snum in(select Snum from SC group by Snum having count(1)>=2)