-
代碼一：
1
2
3
4
? ?
select?c.ci_id,?wm_concat(s.stu_name)?stu_ids
from?pm_ci?c,?pm_stu?s
where?instr(c.stu_ids,?s.stu_id)?>?0
group?by?c.ci_id;
? ?
代碼實(shí)現(xiàn)結(jié)果如下：
發(fā)現(xiàn)和視頻結(jié)果不一樣，沒(méi)有按序號(hào)順序輸入，可能是因?yàn)閣m_concat（）沒(méi)有排序功能？這點(diǎn)不清楚，請(qǐng)大佬指教??！
利用listagg（）代替wm_concat（）就可以輸出正確結(jié)果了。
代碼二：
1
2
3
4
? ?
select?c.ci_id,?listagg(s.stu_name,?',')?within?group?(order?by?s.stu_id)?stu_ids
from?pm_ci?c,?pm_stu?s
where?instr(c.stu_ids,?s.stu_id)?>?0
group?by?c.ci_id;
? ?--------------------------------------------------------------------------
select cstu.ci_id, wm_concat(stu.stu_name) stu_name
?from (select c.ci_id, stu.stu_name
? ? ? ? ?from PM_CI c, PM_STU stu
? ? ? ? where instr(c.stu_ids, stu.stu_id) != 0) cstu
group by cstu.ci_id結(jié)果如下：
查看全部 -
練習(xí)題：
要求查詢(xún)一下的結(jié)果：
1、先創(chuàng)建表及初始化數(shù)據(jù)
?①創(chuàng)建及初始化學(xué)生表PM_STU
create table PM_STU(STU_ID? ? ? ? ? ?VARCHAR2(20) NOT NULL,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? STU_NAME? ? ? VARCHAR2(20));? ? ? ? ? ? ? ??
INSERT INTO? PM_STU VALUES(1,'張三');
INSERT INTO? PM_STU VALUES(2,'李四');
INSERT INTO? PM_STU VALUES(3,'王五');
INSERT INTO? PM_STU VALUES(4,'趙六');?
②創(chuàng)建及初始化選課表PM_CI
? 先創(chuàng)建一個(gè)PM_CI1來(lái)轉(zhuǎn)換成PM_CI
CREATE TABLE PM_CI1(CI_ID? ? ? ? VARCHAR2(20) NOT NULL,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? STU_IDS?? VARCHAR2(20));? ? ? ? ? ? ? ? ?
INSERT INTO PM_CI1 VALUES(1,1);
INSERT INTO PM_CI1 VALUES(1,2);
INSERT INTO PM_CI1 VALUES(1,3);
INSERT INTO PM_CI1 VALUES(1,4);
INSERT INTO PM_CI1 VALUES(2,1);
INSERT INTO PM_CI1 VALUES(2,4);
創(chuàng)建PM_CI，并插入數(shù)據(jù)
CREATE TABLE PM_CI(CI_ID? ? ? ?VARCHAR2(20) NOT NULL,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?STU_IDS?? VARCHAR2(20));
?
INSERT INTO? PM_CI? SELECT CI_ID,WM_CONCAT(STU_IDS)? FROM PM_CI1 GROUP BY CI_ID;
---先做出兩張表的笛卡爾積，再根據(jù)有效的條件instr(a.STU_IDS,b.STU_ID) !=0，過(guò)濾得到每個(gè)科目對(duì)應(yīng)的學(xué)生
? ? ? ?select? a.CI_ID ,b.STU_NAME
??????? from? PM_CI a,PM_STU b
??????? where? ?instr(a.STU_IDS,b.STU_ID) !=0;
? ---使用WM_CONCAT（）函數(shù)，列轉(zhuǎn)行
select? ?CI_ID,? ?WM_CONCAT(STU_NAME) SUT_NAME
from (
??????? select a.CI_ID ,b.STU_NAME
??????? from PM_CI a,PM_STU b
??????? where instr(a.STU_IDS,b.STU_ID) !=0
???????? )
group by CI_ID;
【筆記本scott賬戶(hù)創(chuàng)建pm_cil對(duì)應(yīng)截圖上的pm_ci】
查看全部 -
案列三：
所有員工的入職日期：
注意：列的別名是純數(shù)字的話(huà)必須將列明加上雙引號(hào)
解決方式一：分析過(guò)程，使用函數(shù)解決得到查詢(xún)結(jié)果
解決方式二：使用子查詢(xún)的方式解決，利用Oracle的一張偽表dual表
查看全部 -
案例二：
使用explain plan for 得到Sql語(yǔ)句的執(zhí)行計(jì)劃，查看其耗費(fèi)的系統(tǒng)資源，查看執(zhí)行計(jì)劃：select* from table (dbms_xplan.display)；得到使用相關(guān)子查詢(xún)的方式效果更好一些
查看全部 -
案例一：分頁(yè)顯示員工信息
Oracle數(shù)據(jù)庫(kù)是行式數(shù)據(jù)庫(kù)，只能先取第一條數(shù)據(jù)才能取第二條數(shù)據(jù)
查看全部 -
單行子查詢(xún)中的null值問(wèn)題：若員工表中不存在Tom員工，主查詢(xún)的提交就永遠(yuǎn)為假
多行子查詢(xún)中的null值問(wèn)題：案列：查詢(xún)不是老板的員工。即查詢(xún)下圖的葉子節(jié)點(diǎn)即8條記錄【員工表中MGR列表示該員工的老板員工號(hào) 】
not in 的空值問(wèn)題【a not in {10,20,null}等價(jià)于 a !=10 and a!=20 and a!=null;判斷一個(gè)值是否為空永遠(yuǎn)為假]
員工表中MGR列的員工KING為空值
即查詢(xún)?nèi)缦抡Z(yǔ)句無(wú)返回結(jié)果：
select *?
from emp?
where? empno not in (select mgr from emp)
正確的使用多行子查詢(xún)中不包含空值
select *?
from emp?
where? empno not in (select mgr from emp where mgr is not null);
查看全部 -
非法使用子查詢(xún)，使用單行操作符“=”，子查詢(xún)的返回結(jié)果就不允許是多行
多行操作符in?案例：查詢(xún)部門(mén)名稱(chēng)是SALES和ACCOUNTING的員工信息
實(shí)現(xiàn)方式1：
select *
from emp?
where deptname in (select deptname where dname = 'SALES' or dname = 'ACCOUNTING');
實(shí)現(xiàn)方式2：
select? e.*
from emp e .dept d
where e.deptno = d.deptno and (d. dname = 'SALES' or? d.dname = 'ACCOUNTING');
多行操作符any 案例：查詢(xún)工資比30號(hào)部門(mén)任意一個(gè)員工高的員工信息
實(shí)現(xiàn)方式一：
select *
from emp
where sal? > any (select sal from emp where deptno = 30);
實(shí)現(xiàn)方式二：
select *
from emp
where sal? > (select? min(sal) from emp where deptno = 30);
多行操作符all 案列：查詢(xún)工資比30號(hào)部門(mén)所有員工高的員工信息
實(shí)現(xiàn)方式一：
select *
from emp
where sal? > all (select sal from emp where deptno = 30);
實(shí)現(xiàn)方式二：
select *
from emp
where sal? > (select? max(sal) from emp where deptno = 30);
查看全部 -
單行子查詢(xún)只能使用單行操作符，多行子查詢(xún)只能使用多行操作符
子查詢(xún):返回一條記錄屬于單行子查詢(xún)；多行子查詢(xún)：返回多條查詢(xún)記錄屬于多行子查詢(xún)。
查詢(xún)員工信息要求：職位與7566員工一樣，薪水大于7782員工新水：
select *?
from emp
where job = (select job from emp where empno=7566)? and?
? ? ? ? ? ?sal? > (select sal from emp where empno=7782)
查詢(xún)員工工資最低的信息
select *
from? emp
where sal =??(select? min(sal)? from emp );
查詢(xún)最低工資大于20號(hào)部門(mén)最低工資的部門(mén)號(hào)和部門(mén)的最頂工資
select? deptno ,min(sal)
from emp
group by deptno
having min(sal) >??(select min(sal)?
????????????????????????????????from emp?
????????????????????????????????where deptno = 20)
查看全部 -
deptn部門(mén)號(hào)需要與主查詢(xún)的表中的員工的部門(mén)號(hào)保持一致，利用表別名的方式傳遞進(jìn)來(lái)：
查看全部 -
from 后面的子查詢(xún)
1：select *? from? (selcet empno,ename,sal from emp);
2：select *? from? (selcet empno,ename,sal,sal*12 年薪 from emp);
查看全部 -
不可以使用子查詢(xún)的語(yǔ)句group by:
即下述語(yǔ)句是錯(cuò)誤的
查看全部 -
可以使用子查詢(xún)的位置：select，where,having,from
select 后面的子查詢(xún)語(yǔ)句必須是單行子查詢(xún)語(yǔ)句
select empno,ename,sal,(select job from emp where empno=7839) 第四列 from emp;
having子查詢(xún)語(yǔ)句
from子查詢(xún)語(yǔ)句
查看全部 -
自查詢(xún)需要注意的問(wèn)題
查看全部 -
子查詢(xún)：解決不能一步求解的查詢(xún)
實(shí)例：查詢(xún)比SCOTT的工資高的員工
select *
from emp
where sal > (select sal
???????????????????? from emp
???????????????????? where ename ='scott');
查看全部 -
自連接存在的問(wèn)題：不適合操作大表，【自連接產(chǎn)生的是笛卡爾集】
解決辦法：層次查詢(xún)【本質(zhì)上是單表查詢(xún)】
層次查詢(xún)的原理
from emp?
connect by 上一層的員工號(hào)=老板號(hào)? ?——》等價(jià)于如下所示的語(yǔ)句：
????層次查詢(xún)必須給出從哪一個(gè)節(jié)點(diǎn)開(kāi)始遍歷：
select empno ,ename,sal,mgr
from emp?
connect by prior empno=mgr
start with empno=7566
????若從根節(jié)點(diǎn)開(kāi)始遍歷，可以遍歷整個(gè)樹(shù)，只有根節(jié)點(diǎn)沒(méi)有老板號(hào)，可以這樣寫(xiě)查詢(xún)語(yǔ)句
select empno ,ename,sal,mgr
from emp?
connect by prior empno=mgr
start with empno is null
? ? 層次查詢(xún)有偽列l(wèi)evel，必須查詢(xún)?cè)摿胁拍茱@示出來(lái)
select?level, empno ,ename,sal,mgr
from emp?
connect by prior empno=mgr
start with empno is null
order by 1
查看全部
舉報(bào)