oracle数据库中多列分组查询,Oracle表查询(分组查询,多表查询,子查询,会合查询等)...

--Oracle表查询(分组查询,多表查询,子查询,集合操作等)

--分组函数(max, min, avg, sum, count)

--1.查询所有员工最高和最低工资

select max(sal)最高工资,min(sal)最低工资from emp;

--2.利用子查询查询最高工资的人是谁

select ename,sal from emp where sal = (select max(sal) from emp);

--3. group by(用于对查询的结果分组统计)查询每个部门的平均工资和最高工资

select avg(sal), max(sal), deptno from emp group by deptno;

--4.having(用于限制分组显示结果)

select avg(sal), max(sal), deptno from emp group by deptno having avg(sal) < 2000;

--5.多表查询

--a.查询名字,工资及所在部门的名字

select e.ename, e.sal, d.dname from emp e, dept d where e.deptno = d.deptno;

--b.查询员工的行吗,工资及工资级别

select e.ename, e.sal, s.grade from emp e, salgrade s where e.sal between s.losal ands.hisal;

--自连接 显示员工FORD的上级

select worker.ename, boss.ename from emp worker, emp boss

where worker.mgr = boss.empno and worker.ename = 'FORD';

--在多行子查询中使用all操作符

select ename, sal, deptno from emp

where sal > all(select sal from emp where deptno = 30);

--等价于:

select ename, sal, deptno from emp where sal >

(select max(sal)from emp where deptno = 30);

--在多行子查询中使用any操作符

select ename, sal, deptno from emp

where sal > any(select sal from emp where deptno = 30);

--等价于

select ename, sal, deptno from emp where sal >

(select min(sal) from emp where deptno = 30);

--多列子查询

select deptno, ename, job from emp

where (deptno, job) =(select deptno, job from emp where ename = 'SMITH');

--在from子句中使用子查询 如何显示高于自己部门平均工资的员工信息

select e.ename, e.deptno, e.sal, ds.avgsal from emp e,

(select deptno, avg(sal) avgsal from emp group by deptno) ds

where e.deptno = ds.deptno and e.sal > ds.avgsal;

--用查询结果创建新表

create tablemytable(id, name, sal, job, deptno) as

(select empno, ename, sal, job, deptno from emp);

--合并查询 union取得两个结果的并集,自动去掉重复行,union all则不去除重复行

select ename, sal, job from emp where sal > 2500

union

select ename, sal, job from emp where job = 'MANAGER';

select ename, sal, job from emp where sal > 2500

union all

select ename, sal, job from emp where job = 'MANAGER';

--取交集intersect

select ename, sal, job from emp where sal > 2500

intersect

select ename, sal, job from emp where job = 'MANAGER';

--取差集minus

select ename, sal, job from emp where sal > 2500

minus

select ename, sal, job from emp where job = 'MANAGER';