请简述oracle连接的高级功能,Oracle高级查询

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;