oracle——笔记——分组查询 ——子查询

￡神魔★判官ぃ 2023-10-02 08:14 141阅读 0赞

create table emp2 ( empno number(4),
                        ename varchar2(10),
                        job varchar2(9),
                        mgr number(4),
                        hiredate date,
                        sal number(7,2),
                        comm number(7,2),
                        deptno number(2) 
                     );
    -----------------------------------------------------------------------------------------------------------
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800.00, null, 20);
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600.00, 300.00, 30);
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250.00, 500.00, 30);
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975.00, null, 20);
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250.00, 1400.00, 30);
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850.00, null, 30);
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450.00, null, 10);
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000.00, null, 20);
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000.00, null, 10);
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500.00, 0.00, 30);
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100.00, null, 20);
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950.00, null, 30);
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000.00, null, 20);
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300.00, null, 10);
    
    =============================================================================================================================
    
    create table dept2 ( deptno number(2),
                         dname varchar2(14),
                         loc varchar2(13)
                       );
    
    ----------------------------------------------------
    
    insert into dept2 (DEPTNO, DNAME, LOC)
    values (10, 'ACCOUNTING', 'NEW YORK');
    
    insert into dept2 (DEPTNO, DNAME, LOC)
    values (20, 'RESEARCH', 'DALLAS');
    
    insert into dept2 (DEPTNO, DNAME, LOC)
    values (30, 'SALES', 'CHICAGO');
    
    insert into dept2 (DEPTNO, DNAME, LOC)
    values (40, 'OPERATIONS', 'BOSTON');
    
    ============================================================
    
    create table salgrade2 ( grade number,
                             losal number,
                             hisal number
                           );
    
    --------------------------------------------------
    
    insert into salgrade2 (GRADE, LOSAL, HISAL)
    values (1, 700, 1200);
    
    insert into salgrade2 (GRADE, LOSAL, HISAL)
    values (2, 1201, 1400);
    
    insert into salgrade2 (GRADE, LOSAL, HISAL)
    values (3, 1401, 2000);
    
    insert into salgrade2 (GRADE, LOSAL, HISAL)
    values (4, 2001, 3000);
    
    insert into salgrade2 (GRADE, LOSAL, HISAL)
    values (5, 3001, 9999);
    
    
    ========================================================
    
    select * from emp2;
    
    select * from dept2;
    
    select * from salgrade2;
    
    =====================================
    select * from emp2;        --employee2 员工表
    select * from dept2;       --department2 部门表
    select * from salgrade2;   -- salary grade2 工资等级表
    
    -----------------------------------------------------
    emp2
    
    empno    员工编号
    ename    员工姓名
    job      工作/工种
    mgr      manager上级编号
    hiredate 入职日期
    sal      salary 工资
    comm     奖金/津贴
    deptno   部门编号
    ---------------------
    
    dept2
    
    deptno  部门号
    dname   部门名称
    loc     所在地
    ---------------------------
    salgrade2
    
    grade                   等级
    losal lowest salary     最低工资
    hisal high salary       最高工资
    
    =============================================
    
    
    
    分组查询:
    
    select
    from
    where       分组前的条件(不允许出现分组函数)
    group by    列1,列2,……
    having      分组后的条件(关于分组函数的条件)
    order by
    
    分组函数 :
       avg() sum() max() min() count() wm_concat()
       平均数 求和 最大  最小   统计    列转行
       
      select avg(sal),sum(sal),max(sal),min(sal),count(sal) from emp2;
      select avg(comm),sum(comm),count(comm) from emp2;
      
    分组函数不计算空值
    
    
    
    
    查询每个部门的平均工资:
    
      
    select deptno,avg(sal)
    from emp2
    group by deptno;
    
    
    
    
    
    查询每种工作的最高工资:
    
    select job,max(sal)
    from emp2
    group by job;
      
      
    
    
    
    
    
    
    查询每个部门中每种工作的平均工资:
    
    select deptno,job,avg(sal)
    from emp2
    group by deptno,job
    order by deptno;
          
    注意：出现在select中的列，必须出现在group by语句里；select集合包含于group by集合
      
    
    
    
    
    
    
    
    
    
    查询平均工资大于2000 的部门：
    
    select deptno,avg(sal)
    from emp2
    group by deptno
    having avg(sal) > 2000;
      
      
    
    
    
    
    
    
      
      
    查询平均工资大于2000 的部门信息(号、名称、所在地)：
    
    select d.*,avg(sal)
    from emp2 e,dept2 d
    where e.deptno = d.deptno
    group by d.deptno,d.dname,d.loc
    having avg(sal) > 2000
    order by d.deptno;
    
    
    
    
    
    
    
    
    
    查询有有每一个部门有多少人、人名列表、人数：
    
    select deptno,wm_concat(ename),count(ename)
    from emp2
    group by deptno;
      
    
    
    
    
    ----------------------------------------------------------------------------------------------------------
      
    子查询：单行子查询 多行子查询 多列子查询
     
    
    查询与SCOTT同部门的员工信息：
    
    
    (1)select deptno from emp2 where ename = 'SCOTT';
    (2)select * from emp2 where deptno = 20;
      
    select * from emp2 
    where deptno = (select deptno from emp2 where ename = 'SCOTT');
      
      
      
    查询与JONES 同上级的员工:
    
    
    select * from emp2
    where mgr = (select mgr from emp2 where ename = 'JONES');
    
    
     
    查询工资比MILLER 低，奖金比ALLEN 高的员工信息:
    
    
    select *
    from emp2
    where sal < (select sal from emp2 where ename = 'MILLER') and comm > (select comm from emp2 where ename = 'ALLEN');
       
       
       
    查询与MARTIN 同工作，并且在1981年5 月之前入职的员工:
    
    select *
    from emp2
    where job = (select job from emp2 where ename = 'MARTIN') and hiredate < to_date('1981-5-1','YYYY-MM-DD');
       
       
    
    查询上级是JONES 的员工信息:
    
    select * from emp2
    where mgr = (select empno from emp2 where ename = 'JONES');
      
    
    
    查询工资比平均工资高的员工:
    
    select * from emp2
    where sal > (select avg(sal) from emp2);
     
    
    
    查询工资比10 部门平均工资高的员工:
    
    select * from emp2
    where sal > (select avg(sal) from emp2 where deptno = 10);
     
    
    
    使用子查询，查询SALES 部门的员工信息:
    
    select * 
    from emp2 e,dept2 d
    where e.deptno = d.deptno and d.dname = 'SALES';
    
       
    select * from emp2 
    where deptno = (select deptno from dept2 where dname = 'SALES');
    
    
    
    
    
    
    
    
    
    
    
    
    
    ------------------------------------------------------------------------------------------
       
    
    
    
    
    
    
    
    
     
    多行子查询:   >all    <all    >any     <any     =any     in()
     
    查询工资比30 部门所有员工工资都要高的员工信息:
    
    select * from emp2
    where sal >all (select sal from emp2 where deptno = 30);
    
    
    select * from emp2
    where sal > (select max(sal) from emp2 where deptno = 30);
    
    
      
      >all  大于最大
      
      <all  小于最小
      
      >any  大于最小
      
      <any  小于最大
      
      =any
      
      in()
      
      
      
    select * from emp2 where sal in(select sal from emp2 where deptno = 30);
    
    select * from emp2 where sal in(1600,1250,2850,1500,950);
    
    
    
    查询哪个部门没有员工:存在于dept表，但是不存在于emp表
    
    
    select * from dept2
    where deptno not in(select distinct deptno from emp2);
      
                 select * from emp2;  --deptno 不能有空值
      
    
    
    
    
    
    
    多列查询举例：
    
    select * from emp2
    where (sal,job) = (select sal,job from emp2 where ename = 'SCOTT' );
    
    
    
    
    
      
    查询每个部门的部门信息和部门人数(考虑40 部门)：
    
    select d.*,count(e.empno)
    from emp2 e right join dept2 d
    on e.deptno = d.deptno
    group by d.deptno,d.dname,d.loc;
     
    
    
    select d.deptno,nvl(con,0)
    from (select deptno,count(empno) con from emp2 group by deptno) t right join dept2 d
    on t.deptno = d.deptno;
     
     
     
     
     
     
    P237-238 rownum
     
     
    不使用组函数，查询最高工资：
    
    
    rownum 存在查询结果中，不属于任何一张表
    rownum 针对查询结果排序，
            序号从1开始
    rownum 比较运算符  = 1   <   <=
     
    select * from (select * from emp2 order by sal desc) t
    where rownum = 1;
    
    
    
    
    
    
    
    
    
    
    
    查询工资第二高到第八高的员工信息：
     
    (1)
     
     select * from (select * from emp2 order by sal desc) t
     where rownum <= 8
     minus
     select * from (select * from emp2 order by sal desc) t
     where rownum = 1; 
     
     
    (2)
    -- select t.*,rownum r from ( select * from emp2 order by sal desc  ) t ；
    
    select w.*  from ( select t.*,rownum  r from ( select * from emp2 order by sal desc  ) t ) w where  r between 2 and 8;
    
    
    
    
    
    
    
       
     3)排序函数
     
     select rank() over(order by sal desc) ran,
            dense_rank() over(order by sal desc) dran,
            row_number() over(order by sal desc) rown,
      emp.*
     from emp;