2回答

• 

  大王叫我来巡山570127

  2020-03-24 10:55:07

  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;

  
  

  
  

  0 1
• 

  大王叫我来巡山570127

  2020-03-24 01:11:44

  示例二

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

  
  

  方法三：分析函数

  select *

  from (

        select empno,ename,sal,

                 avg(sal) over(partition by deptno) avg_sal

          from emp) 

  where sal > avg_sal

  order by avg_sal;

  
  

  0 0