oracle分组查询砺而行,oracle——笔记——分组查询 ——子查询

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 any

查询工资比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 大于最大

>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;

标签：insert,sal,查询,emp2,oracle,分组,deptno,where,select

来源： https://www.cnblogs.com/xiaobaibailongma/p/12258098.html