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;

示例二

• 找到员工表中薪水大于本部门平均薪水的员工

方法三：分析函数

select *

from (

      select empno,ename,sal,

               avg(sal) over(partition by deptno) avg_sal

        from emp) 

where sal > avg_sal

order by avg_sal;