oracle子查詢

標籤：學習   null   需要   情況   round   其他   成績表   括弧   檢索   

小峰91

子查詢：當一個查詢的結果時另一個查詢的條件時，即嵌入在其它SQL語句中的select語句，也成為巢狀查詢。

 子查詢可以返回單行結果，可以返回多行結果，也可以不返回結果

 使用子查詢注意事項：

1、子查詢可以嵌套多層

2、子查詢需要圓括弧（）括起來

3、當在DDL語句中引用子查詢時，可以帶有order  by語句，但當在where子句中，set子句中引用子查詢時，不能帶有order  by語句

 根據查詢的結果（內部巢狀查詢的結果）把子查詢的類型分為單行子查詢和多行子查詢

 1、單行子查詢：

只返回一行資料的子查詢語句

單行操作符：>   >=   <    <=   < >   =

例：SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE empno=7876)AND sal>(SELECT sal FROM emp WHERE empno=7521);

2、多行子查詢：

是指返回多行資料的子查詢語句，使用多行操作符：

操作符	描述
In	等於列表中的任何一個
any	子查詢返回的任意一個值比較，相同的還有some
all	和子查詢返回的所有值比較
Exists

 

此時不允許對其使用單行記錄比較子

select * from emp where sal > (select avg(sal) from emp group by deptno);//非法

 ①IN操作符

SELECT * FROM emp t WHERE t.deptno IN(30,40);

②ALL操作符   如果在where條件中加入 > all ，意思是大於每一個，也就是大於最大的

SELECT * FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=30); 

③ANY即任何一個。如果在where條件中加入>any ，意思是大於任何一個，也就是大於最小的

SELECT * FROM emp WHERE sal>ANY(SELECT sal FROM emp WHERE deptno=30);

④SOME即一些。和any用法基本相同。用any的地方都可以用some代替。不過some大多用在=操作中。表示等於所選集合中的任何一個。當然any也可以用於=操作中，效果和some相同。

SELECT * FROM emp e WHERE e.sal=SOME(SELECT sal FROM emp WHERE deptno=30);

⑤exists

SELECT * FROM emp WHERE EXISTS(SELECT * FROM emp WHERE deptno=30);

 3、多列子查詢：

多列子查詢返回多列結果的內部select語句，多列子查詢中的列的比較有成對比較和不
成對比較，兩種方法。多列子查詢分為成對比較多列子查詢和非成對比較多列子查詢。SELECT * FROM emp WHERE (deptno,job)=(SELECT deptno,job FROM emp WHERE ename=‘WARD‘);

Nvl函數：如果是空值就替換成另一個值

如nvl(欄位，‘x’)欄位值等於null就這個函數得到的結果就是‘x‘，一般用於存在空值比較的情況下，比如欄位a與欄位b都是int型，其中一個等於null另一個為非空值，你使用a<>b是不成立的，使用此條件查詢你將丟失這條本來不相等的資料，可以如此用法nvl(欄位，-1)<>nvl(欄位，-1)，這樣就可以得到想要查詢的資料，當然查詢資料的前提是此欄位值不能有-1值才能用這種寫法

 ①成對比較

查詢那些職員是工資為所任職位最高的

SELECT * FROM emp WHERE (sal,NVL(comm,-1)) IN (SELECT sal,NVL(comm,-1) FROM emp WHERE deptno=30);

這裡，子查詢返回每一種職位的最高工資和職位的名稱。之後主查詢的每一行中的工資和職位都要與子查詢返回列表中的最高工資和職位相比較，只有當兩者同時完全符合時才顯示該資料行。

 ②非成對比較

若我想知道哪些職員的工資與某一職位的最高工資相同，則：

SELECT empno,ename,sal,job

FROM emp

WHERE sal IN (SELECT MAX(sal)

FROM emp

GROUP BY job)

AND job IN (SELECT DISTINCT job 

FROM emp);

這是一種典型的非成對比較的多列子查詢。

create table student(  sno varchar2(10) primary key,  sname varchar2(20),  sage number(2),  ssex varchar2(5)  );  create table teacher(  tno varchar2(10) primary key,  tname varchar2(20)  );  create table course(  cno varchar2(10),  cname varchar2(20),  tno varchar2(20),  constraint pk_course primary key (cno,tno)  );  create table sc(  sno varchar2(10),  cno varchar2(10),  score number(4,2),  constraint pk_sc primary key (sno,cno)  );  #/*******初始化學生表的資料******/  insert into student values (‘s001‘,‘張三‘,23,‘男‘);  insert into student values (‘s002‘,‘李四‘,23,‘男‘);  insert into student values (‘s003‘,‘吳鵬‘,25,‘男‘);  insert into student values (‘s004‘,‘琴沁‘,20,‘女‘);  insert into student values (‘s005‘,‘王麗‘,20,‘女‘);  insert into student values (‘s006‘,‘李波‘,21,‘男‘);  insert into student values (‘s007‘,‘劉玉‘,21,‘男‘);  insert into student values (‘s008‘,‘蕭蓉‘,21,‘女‘);  insert into student values (‘s009‘,‘陳蕭曉‘,23,‘女‘);  insert into student values (‘s010‘,‘陳美‘,22,‘女‘);  commit;  #/******************初始化教師表***********************/  insert into teacher values (‘t001‘, ‘劉陽‘);  insert into teacher values (‘t002‘, ‘諶燕‘);  insert into teacher values (‘t003‘, ‘胡明星‘);  commit;  #/***************初始化課程表****************************/  insert into course values (‘c001‘,‘J2SE‘,‘t002‘);  insert into course values (‘c002‘,‘Java Web‘,‘t002‘);  insert into course values (‘c003‘,‘SSH‘,‘t001‘);  insert into course values (‘c004‘,‘Oracle‘,‘t001‘);  insert into course values (‘c005‘,‘SQL SERVER 2005‘,‘t003‘);  insert into course values (‘c006‘,‘C#‘,‘t003‘);  insert into course values (‘c007‘,‘JavaScript‘,‘t002‘);  insert into course values (‘c008‘,‘DIV+CSS‘,‘t001‘);  insert into course values (‘c009‘,‘PHP‘,‘t003‘);  insert into course values (‘c010‘,‘EJB3.0‘,‘t002‘);  commit;  #/***************初始化成績表***********************/  insert into sc values (‘s001‘,‘c001‘,78.9);  insert into sc values (‘s002‘,‘c001‘,80.9);  insert into sc values (‘s003‘,‘c001‘,81.9);  insert into sc values (‘s004‘,‘c001‘,60.9);  insert into sc values (‘s001‘,‘c002‘,82.9);  insert into sc values (‘s002‘,‘c002‘,72.9);  insert into sc values (‘s003‘,‘c002‘,81.9);  insert into sc values (‘s001‘,‘c003‘,59);  

#1、查詢“c001”課程比“c002”課程成績高的所有學生的學號；  select *  from sc a, sc b where a.sno=b.sno and a.cno=‘c001‘ and b.cno=‘c002‘ and a.score>b.score;  #2、查詢平均成績大於60 分的同學的學號和平均成績；  select st.sname,ss.sco from student st,(select sno,avg(score)sco from sc group by sno)ss where st.sno=ss.sno and sco>=60;  #3、查詢所有同學的學號、姓名、選課數、總成績；  select st.sname,ss.* from student st,(select sno,count(score)sco,sum(score) from sc group by sno)ss where st.sno=ss.sno;  #4、查詢姓“劉”的老師的個數；  select count(*) from teacher where tname like ‘%劉%‘;  #5、查詢沒學過“諶燕”老師課的同學的學號、姓名；  select sc.sno,acno.* from sc sc,(select co.cno from course co,(select tno from teacher where tname=‘諶燕‘)tn where co.tno<>tn.tno)acno where acno.cno=sc.cno;  #6、查詢學過“c001”並且也學過編號“c002”課程的同學的學號、姓名；  select sa.sname,sc.sno from sc sc,(select st.sname,s.* from student st,(select sno,cno from sc where cno in(‘c001‘))s where st.sno=s.sno)sa where sc.sno=sa.sno and sc.cno=‘c002‘;  #7、查詢學過“諶燕”老師所教的所有課的同學的學號、姓名；  select distinct st.sname from student st,sc sc join (select cno from teacher te join course co on(te.tno=co.tno) where te.tname=‘諶燕‘)aca on(sc.cno=aca.cno) where st.sno=sc.sno;   #9、查詢所有課程成績小於60 分的同學的學號、姓名；  select st.sname,sc.sno from student st join sc sc on(st.sno=sc.sno)where sc.score<60;  #10、查詢沒有學全所有課的同學的學號、姓名；  select sname,st.sno,scn.cn from student st join (select distinct sno,count(*)cn from sc group by sc.sno)scn on(st.sno=scn.sno) where scn.cn=(select distinct count(cno) from course);  select st.sname,st.sno from student st join (select sc.sno sn,count(sc.cno) cou from sc group by sc.sno) scs on st.sno=scs.sn where scs.cou <(select count(cno) from course);  #11、查詢至少有一門課與學號為“s001”的同學所學相同的同學的學號和姓名；  select distinct st.sno, sname    from student st    join sc sc on (st.sno = sc.sno)   where sc.cno in (select cno from sc where sno = ‘s001‘) and sc.sno<>‘s001‘;#13、把“SC”表中“諶燕”老師教的課的成績都更改為此課程的平均成績；  #update sc sc set sc.score=update sc sc set sc.score=(select tc.cno,round(avg(sc.score),2)av from (select * from teacher te join course co on(te.tno=co.tno) where te.tname=‘諶燕‘)tc join sc sc on(tc.cno=sc.cno) group by tc.cno)cnn.score where sc.cno=cnn.av;  select tc.cno,round(avg(sc.score),2) from (select * from teacher te join course co on(te.tno=co.tno) where te.tname=‘諶燕‘)tc join sc sc on(tc.cno=sc.cno) group by tc.cno;  #14、查詢和“s001”號的同學學習的課程完全相同的其他同學學號和姓名；  select cno from sc where sno=‘s001‘;  #15、刪除學習“諶燕”老師課的SC 表記錄；  delete from sc where sc.cno in(select cno from teacher te join course co on(te.tno=co.tno) where te.tname=‘諶燕‘)  #17、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分  select cno,max(score),min(score) from sc group by cno;  #18、按各科 平均成績 從 低到高 和 及格率的百分數 從高到低順序  #select count(score) from sc where score>=60 group by cno ;  select cno,avg(score)acs,savg.cnu,(count(score)-savg.cnu)/count(score)*100 from sc,  (select count(score)cnu,avg(score)acs2 from sc where score<60 order by acs2 desc)savg   group by savg.cnu,cno order by acs desc;  #21、查詢各科成績前三名的記錄:(不考慮成績並列情況)  select * from (select S.*,rownum rn from (select * from sc order by score desc)S where rownum<=3)where rn>=1;  #22、查詢每門課程被選修的學生數  select cno,count(cno) from sc group by cno;  #23、查詢出只選修了一門課程的全部學生的學號和姓名  select st.sname,st.sno from student st join   (select distinct sc.* from sc sc join (select sno,count(cno)cnum from sc group by sno)ss on(sc.sno=ss.sno) where ss.cnum=1)ssb   on(st.sno=ssb.sno);  #24、查詢男生、女生人數  select ssex,count(ssex) from student st group by ssex;  #25、查詢姓“張”的學生名單  select sname from student where sname like ‘%張%‘;  #26、查詢同名同性學生名單，並統計同名人數  select st.* from student st join (select sname,count(sname)cnum from student group by sname)scomm on(st.sname=scomm.sname) where scomm.cnum>1;  #27、1981 年出生的學生名單(註：Student 表中Sage 列的類型是number)  select * from student where to_char(sysdate,‘yyyy‘)-sage =‘1981‘;  #28、查詢每門課程的平均成績，結果按平均成績升序排列，平均成績相同時，按課程號降序排列  select cno,avg(score)lim from sc group by cno order by lim asc;  #29、查詢平均成績大於85 的所有學生的學號、姓名和平均成績  select st.sname,st.sno,savg.lim from student st join (select sno,avg(score)lim from sc group by sno)savg on(st.sno=savg.sno) where savg.lim>85;  #30、查詢課程名稱為“資料庫”，且分數低於60 的學生姓名和分數  select distinct st.sname,cs.score from student st join (select * from course co join sc sc on(co.cno=sc.cno))cs on(st.sno=cs.sno)where cs.score<60 and cs.cname=‘資料庫‘;  #31、查詢所有學生的選課情況；  select distinct st.sname,cs.cname from student st join (select * from course co join sc sc on(co.cno=sc.cno))cs on(st.sno=cs.sno);  #32、查詢任何一門課程成績在70 分以上的姓名、課程名稱和分數；  select distinct st.sname,cs.score,cs.cname from student st join (select * from course co join sc sc on(co.cno=sc.cno))cs on(st.sno=cs.sno)where cs.score>70;  #33、查詢不及格的課程，並按課程號從大到小排列  select * from sc where score<60 order by sno desc;  #34、查詢課程編號為c001 且課程成績在80 分以上的學生的學號和姓名；  select st.sname,st.sno from student st join (select * from sc where cno=‘c001‘ and score>80)snum on(st.sno=snum.sno);  #35、求選了課程的學生人數  select count(ss.sno) from (select distinct sno from sc)ss;  #36、查詢選修“諶燕”老師所授課程的學生中，成績最高的學生姓名及其成績  select st.sname,cnum.score from student st join  (select distinct * from sc sc where cno in(select distinct cno from course co join   (select tno from teacher where tname=‘諶燕‘)ten on(co.tno=ten.tno)) order by score desc)cnum on(st.sno=cnum.sno) where rownum=1;  #37、查詢各個課程及相應的選修人數  #(select cno,count(cno) from sc group by cno);  select co.cname,cns.cnum from course co join (select cno,count(cno)cnum from sc group by cno)cns on(co.cno=cns.cno);  #39、查詢每門功課成績最好的前兩名  select cno from sc order by score desc group by cno;  #40、統計每門課程的學生選修人數（超過10 人的課程才統計）。要求輸出課程號和選修人數，查詢結果按人數降序排列，若人數相同，按課程號升序排列  #41、檢索至少選修兩門課程的學生學號  select sno from (select sno,count(cno)cnum from sc group by sno) where cnum>=2;  #42、查詢全部學生都選修的課程的課程號和課程名  #select count(*) from (select distinct sno from sc);  #select cno,count(cno)cns from sc group by cno;  #select cno from (select count(*)num from (select distinct sno from sc))cs1,(select cno,count(cno)cns from sc group by cno)cs2 where cs2.cns=cs1.num;  select cname,scs.cno from course co join   (select cno from (select count(*)num from (select distinct sno from sc))cs1,  (select cno,count(cno)cns from sc group by cno)cs2 where cs2.cns=cs1.num)scs on(co.cno=scs.cno);  #43、查詢沒學過“諶燕”老師講授的任一門課程的學生姓名  #select co.cno from course co join (select tno from teacher where tname=‘諶燕‘)tn on(co.tno=tn.tno);  select sname from student where sno not in  (select distinct sno from sc where cno in(select co.cno from course co join   (select tno from teacher where tname=‘諶燕‘)tn on(co.tno=tn.tno)));  #44、查詢兩門以上不及格課程的同學的學號及其平均成績  #select sno,count(sno),avg(score) from sc where score < 60 group by sno;  select * from (select sno,count(sno)cnum,avg(score) from sc where score < 60 group by sno)avs where avs.cnum>1;  #45、檢索“c004”課程分數小於60，按分數降序排列的同學學號  select sno from sc where cno=‘c004‘ and score<60 order by score desc;  #46、刪除“s002”同學的“c001”課程的成績  delete from sc where sno=‘s002‘ and cno=‘c001‘;  

oracle子查詢