oracle子查詢

子查詢：在一個查詢的內部包括另外一個查詢。

普通子查詢

-- 查詢出比7654工資還高的全部雇員的信息
select * from emp e where e.sal > (select sal from emp where empno = 7654);

-- 查詢出工資比7654高,同時與7788從事相同工作的全部雇員的信息
select * from emp e
where e.sal > (select sal from emp where empno = 7654)
and e.job = (select job from emp where empno = 7788);

-- 查詢出工資最低的雇員姓名、工作、工資
select e.ename, e.job, e.sal from emp e
where e.sal = (select min(sal) from emp);

in 查詢

in 關鍵字用來匹配一個集合中的記錄

-- 查詢雇員編號為1234,2345，7369,7900的雇員信息
select * from emp where empno in(1234, 2345, 7369, 7900);

-- 查詢雇員編號不是 1234,2345，7369,7900的雇員信息
select * from emp where empno not in(1234, 2345, 7369, 7900);

-- 查詢每個部門的最低工資對應的員工信息
select * from emp where sal in (select min(sal) from emp group by deptno);

any關鍵字

any：表示任意的。

< any 比子查詢返回的任意一個結果小即可，即小於返回結果的最大值
<喎?http://www.Bkjia.com/kf/ware/vc/" target="_blank" class="keylink">vcD4KPHA+PSBhbnkgCrrN19Oy6dGv1tDIztLi0ru49r3hufvP4LXIvLS/yaOsz+C1sdPaaW48YnI+CjwvcD4KPHA+Jmd0OyBhbnkgCrHI19Oy6dGvt7W72LXEyM7S4tK7uPa94bn7tPO8tL/Jo6y8tLTz09q3tbvYveG5+7XE1+7QoSYjMjA1NDA7PC9wPgoKPHA+PHByZSBjbGFzcz0="brush:sql;">-- 查詢每個部門的最低工資 select min(sal) min_sal from emp group by deptno;

sal 大於 any （每個部門最低工資），即大於返回結果的最小值

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

sal = any （每個部門最低工資），即 和子查詢中每個結果相等，同in

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

sal < any （每個部門最低工資），即大於返回結果的最大值

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

all關鍵宗喎?http://www.Bkjia.com/kf/yidong/wp/" target="_blank" class="keylink">WPC9oMT4KCjxwPmFsbKO6se3Kvsv509C1xKGjPC9wPgo8cD4mbHQ7IGFsbCAKscjX07Lp0a+3tbvYtcTL+dPQtcS94bn7trzQoaOsvLTQodPat7W72L3hufu1xNfu0KEmIzIwNTQwOzxicj4KPC9wPgo8cD4mZ3Q7IGFsbCAKscjX07Lp0a+3tbvYtcTL+dPQtcS94bn7try086OsvLS089Pat7W72L3hufu1xNfutPMmIzIwNTQwOzxicj4KPC9wPgo8cD49IGFsbCAKzt7S4tLlo6zC37ytyc/SsrK7s8nBojxicj4KPC9wPgoKPHA+sunRr7mk18rU2jIwMDAgtb0gMzUwMLXEuaTXyrbOtcS5pNfKvK+6zzxicj4KPC9wPgo8cD48cHJlIGNsYXNzPQ=="brush:sql;">select distinct sal from emp where sal between 2000 and 3500;

> all (工資在2000 到 3500的工資段的工資集合) ，即大於最大值

select * from emp where sal > all(select distinct sal from emp where sal between 2000 and 3500);

< all (工資在2000 到 3500的工資段的工資集合)，即小於最小值

select * from emp where sal < all(select distinct sal from emp where sal between 2000 and 3500);