CREATE TABLE S (SNO VARCHAR2(10),SNAME VARCHAR2(10));
CREATE TABLE C (CNO VARCHAR2(10),CNAME VARCHAR2(10),CTEACHER VARCHAR2(10));
CREATE TABLE SC (SNO VARCHAR2(10),CNO VARCHAR2(10));INSERT INTO S VALUES('001','stu1');
INSERT INTO S VALUES('002','stu2');
INSERT INTO S VALUES('003','stu3');INSERT INTO C VALUES('11','c1','Sampson');
INSERT INTO C VALUES('22','c2','李明');
INSERT INTO C VALUES('33','c3','李明');INSERT INTO SC VALUES('001','11');
INSERT INTO SC VALUES('002','22');
INSERT INTO SC VALUES('003','33');SELECT DISTINCT T1.SNO,T1.SNAME
FROM S T1
LEFT JOIN SC T3
ON T1.SNO = T3.SNO
LEFT JOIN C T2
ON T3.CNO = T2.CNO
WHERE T2.CTEACHER != '李明';
CREATE TABLE C (CNO VARCHAR2(10),CNAME VARCHAR2(10),CTEACHER VARCHAR2(10));
CREATE TABLE SC (SNO VARCHAR2(10),CNO VARCHAR2(10));INSERT INTO S VALUES('001','stu1');
INSERT INTO S VALUES('002','stu2');
INSERT INTO S VALUES('003','stu3');INSERT INTO C VALUES('11','c1','Sampson');
INSERT INTO C VALUES('22','c2','李明');
INSERT INTO C VALUES('33','c3','李明');INSERT INTO SC VALUES('001','11');
INSERT INTO SC VALUES('002','22');
INSERT INTO SC VALUES('003','33');SELECT DISTINCT T1.SNO,T1.SNAME
FROM S T1
LEFT JOIN SC T3
ON T1.SNO = T3.SNO
LEFT JOIN C T2
ON T3.CNO = T2.CNO
WHERE T2.CTEACHER != '李明';
解决方案 »
- 找出pp2表中num重复的记录
- 像大家请教一下,Oracle多个表的自动标识列的问题
- scott为什么不能访问SYS 下的某些表?
- Oracle ORA-00972:Identification too long
- 急急
- Oracle模糊查询,不用like还能用什么?有什么可以有like一样的效果?
- oracle小问题,请高手帮忙!问题解决便可结贴!
- 那有oracle方面的电子书看呀!!!!
- 再oracle开发中,什么开发工具最好那〉?
- 求一个存储过程的最优写法
- 关于goldengate Director 客户端admin无法登陆的问题?
- oracle:system收回了用户对表的查询权限,用户为什么还能查询
002','stu2' 有选 李明的课 ,但也出现在结果集里?
请高手解释
真的不行?
SELECT DISTINCT S.SNO,S.SNAME
FROM S,C,SC
WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND C.CTEACHER<>'李明'
如果李明老师的课只有一门
select s.sno,c.cno
from s,c
where c.cteacher='李明'
minus
select sno,cno不止一门:
select sno from(
select sc.sno,c.cno
from sc,c
where sc.cno=c.cno(+)
and c.cteacher(+)='李明'
)group by sno
having count(cno)=0
select sno,sname from s --所有学生
minus
select distinct s.sno,s.sname from s,c,sc where s.sno=sc.sno and sc.cno=c.cno and c.cteacher='李明'--选李明的课的学生