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);
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。