select a.dept_id, a.dept_name from dept a where not exits(select 1 from emp b where a.dept_id = b.dept_id and b.money between 2000 and 3000 ) ;
--晕,看反了 select a.dept_id, a.dept_name from dept a where not exits(select 1 from emp b where a.dept_id = b.dept_id and (b.money < 2000 or b.money > 3000) ) ;
select a.dept_id, a.dept_name from dept a where exits (select 1 from emp b where a.dept_id = b.dept_id and b.money >= 2000 and b.money <=3000 )
3楼的结果是错误的。必须求反的。即只要一个部门的一条记录不满足工资在2000到3000之间就把这个部门排除。 这是我写的select * from dept where deptno not in (select deptno from emp where sal not between 2000 and 3000 group by deptno);跟2楼差不错。不过有一个bug,40号部门是没有员工的。
select a.dept_id, a.dept_name
from dept a
where not exits(select 1 from emp b
where a.dept_id = b.dept_id
and b.money between 2000 and 3000
)
;
--晕,看反了
select a.dept_id, a.dept_name
from dept a
where not exits(select 1 from emp b
where a.dept_id = b.dept_id
and (b.money < 2000 or b.money > 3000)
)
;
select a.dept_id, a.dept_name
from dept a
where exits (select 1
from emp b
where a.dept_id = b.dept_id
and b.money >= 2000 and b.money <=3000
)
这是我写的select *
from dept
where deptno not in (select deptno from emp where sal not between 2000 and 3000 group by deptno);跟2楼差不错。不过有一个bug,40号部门是没有员工的。
SELECT * FROM DEPT D INNER JOIN EMP E ON D.DEPTNO=E.DEPTNO WHERE E.SAL >= 2000 AND E.SAL <= 3000;
DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- -------------- ------------- ---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
20 RESEARCH DALLAS 7566 JONES MANAGER 7839 02-4月 -81 2975 20
30 SALES CHICAGO 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
20 RESEARCH DALLAS 7902 FORD ANALYST 7566 03-12月-81 3000 20