代码一：

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;

代码实现结果如下：

发现和视频结果不一样，没有按序号顺序输入，可能是因为wm_concat（）没有排序功能？这点不清楚，请大佬指教！！

利用listagg（）代替wm_concat（）就可以输出正确结果了。

代码二：

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

结果如下：

练习题：

要求查询一下的结果：

1、先创建表及初始化数据

 ①创建及初始化学生表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,'赵六'); 

②创建及初始化选课表PM_CI

  先创建一个PM_CI1来转换成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);

创建PM_CI，并插入数据

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;

---先做出两张表的笛卡尔积，再根据有效的条件instr(a.STU_IDS,b.STU_ID) !=0，过滤得到每个科目对应的学生

       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（）函数，列转行

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账户创建pm_cil对应截图上的pm_ci】

案列三：

所有员工的入职日期：

注意：列的别名是纯数字的话必须将列明加上双引号

解决方式一：分析过程，使用函数解决得到查询结果

解决方式二：使用子查询的方式解决，利用Oracle的一张伪表dual表

案例二：

使用explain plan for 得到Sql语句的执行计划，查看其耗费的系统资源，查看执行计划：select* from table (dbms_xplan.display)；得到使用相关子查询的方式效果更好一些

案例一：分页显示员工信息

Oracle数据库是行式数据库，只能先取第一条数据才能取第二条数据

单行子查询中的null值问题：若员工表中不存在Tom员工，主查询的提交就永远为假

多行子查询中的null值问题：案列：查询不是老板的员工。即查询下图的叶子节点即8条记录【员工表中MGR列表示该员工的老板员工号 】

not in 的空值问题【a not in {10,20,null}等价于 a !=10 and a!=20 and a!=null;判断一个值是否为空永远为假]

员工表中MGR列的员工KING为空值

即查询如下语句无返回结果：

select * 

from emp 

where  empno not in (select mgr from emp)

正确的使用多行子查询中不包含空值

select * 

from emp 

where  empno not in (select mgr from emp where mgr is not null);

非法使用子查询，使用单行操作符“=”，子查询的返回结果就不允许是多行

多行操作符in 案例：查询部门名称是SALES和ACCOUNTING的员工信息

实现方式1：

select *

from emp 

where deptname in (select deptname where dname = 'SALES' or dname = 'ACCOUNTING');

实现方式2：

select  e.*

from emp e .dept d

where e.deptno = d.deptno and (d. dname = 'SALES' or  d.dname = 'ACCOUNTING');

多行操作符any 案例：查询工资比30号部门任意一个员工高的员工信息

实现方式一：

select *

from emp

where sal  > any (select sal from emp where deptno = 30);

实现方式二：

select *

from emp

where sal  > (select  min(sal) from emp where deptno = 30);

多行操作符all 案列：查询工资比30号部门所有员工高的员工信息

实现方式一：

select *

from emp

where sal  > all (select sal from emp where deptno = 30);

实现方式二：

select *

from emp

where sal  > (select  max(sal) from emp where deptno = 30);

单行子查询只能使用单行操作符，多行子查询只能使用多行操作符

子查询:返回一条记录属于单行子查询；多行子查询：返回多条查询记录属于多行子查询。

查询员工信息要求：职位与7566员工一样，薪水大于7782员工新水：

select * 

from emp

where job = (select job from emp where empno=7566)  and 

           sal  > (select sal from emp where empno=7782)

查询员工工资最低的信息

select *

from  emp

where sal =  (select  min(sal)  from emp );

查询最低工资大于20号部门最低工资的部门号和部门的最顶工资

select  deptno ,min(sal)

from emp

group by deptno

having min(sal) >  (select min(sal) 

                                from emp 

                                where deptno = 20)

deptn部门号需要与主查询的表中的员工的部门号保持一致，利用表别名的方式传递进来：

from 后面的子查询

1：select *  from  (selcet empno,ename,sal from emp);

2：select *  from  (selcet empno,ename,sal,sal*12 年薪 from emp);

不可以使用子查询的语句group by:

即下述语句是错误的

可以使用子查询的位置：select，where,having,from

select 后面的子查询语句必须是单行子查询语句

select empno,ename,sal,(select job from emp where empno=7839) 第四列 from emp;

having子查询语句

from子查询语句

自查询需要注意的问题

子查询：解决不能一步求解的查询

实例：查询比SCOTT的工资高的员工

select *

from emp

where sal > (select sal

                     from emp

                     where ename ='scott');

自连接存在的问题：不适合操作大表，【自连接产生的是笛卡尔集】

解决办法：层次查询【本质上是单表查询】

层次查询的原理

from emp 

connect by 上一层的员工号=老板号   ——》等价于如下所示的语句：

    层次查询必须给出从哪一个节点开始遍历：

select empno ,ename,sal,mgr

from emp 

connect by prior empno=mgr

start with empno=7566

    若从根节点开始遍历，可以遍历整个树，只有根节点没有老板号，可以这样写查询语句

select empno ,ename,sal,mgr

from emp 

connect by prior empno=mgr

start with empno is null

    层次查询有伪列level，必须查询该列才能显示出来

select level, empno ,ename,sal,mgr

from emp 

connect by prior empno=mgr

start with empno is null

order by 1