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

oracle學習篇六：子查詢