您的位置:首页 - 教程 - Oracle - 正文
oracle学习篇六:子查询

-- 1.查询比7654工资要高的员工

select * from emp where sal>(select sal from emp where empno=7654);

---2.查询最低工资的员工信息
select * from emp where sal=(select min(sal) from emp);

------------查询出,部门名称,部门员工数,部门平均工资,部门最低收入的人员姓名,和最高收入的人员
select d.dname,t1.c,t1.avgSal,t1.maxsal,t1.minsal from dept d,(select deptno,count(empno) c,avg(sal) avgSal,max(sal) maxsal,min(sal) minsal from emp group by deptno) t1
where d.deptno=t1.deptno


----ANY函数使用,=any 与in 相同
select * from emp where sal in(select min(sal) from emp group by deptno);

-- > any 比最小的值要大
select * from emp where sal>any(select min(sal) from emp group by deptno);

-- < any 比最大的值要小。
select * from emp where sal < any(select min(sal) from emp group by deptno);

select * from emp where sal < any(select min(sal) from emp group by deptno);

---< all 比最小的还小
select * from emp where sal > ALL(select max(sal) from emp group by deptno);

---> all 比最大的还大
select * from emp where sal < ALL(select max(sal) from emp group by deptno);


评论: