表结构:DEPT(dept_id number(2) /*部门编号*/
dept_name varchar2(40) /*部门名称*/
)
EMP(
emp_id number(6) /*员工编号*/
emp_name varchar2(20) /*员工名称*/
dept_id number(2) /*部门编号*/
)
要求:查询部门人数在5人以上的员工信息“员工编号,员工姓名,部门名称,部门人数”
dept_name varchar2(40) /*部门名称*/
)
EMP(
emp_id number(6) /*员工编号*/
emp_name varchar2(20) /*员工名称*/
dept_id number(2) /*部门编号*/
)
要求:查询部门人数在5人以上的员工信息“员工编号,员工姓名,部门名称,部门人数”
select *
from (select empno,ename,dname,count(empno) over(partition by emp.deptno) cnt
from emp,dept
where emp.deptno=dept.deptno)
where cnt>=5
from emp t1,
(select b.dept_id, b.dept_name, count(*) countdept
from emp a, dept b
where a.dept_id = b.dept_id
group by b.dept_id, b.dept_name
having count(*) >= 5) t2
where t1.dept_ = t2.dept
select *
from (select empno,ename,dname,count(empno) over(partition by emp.deptno) cnt
from emp,dept
where emp.deptno=dept.deptno)
where cnt>=5select * from
(select deptno, count(emp_id) cnt from emp, dept where emp.deptno=dept.deptno) tabCnt, emp
where tabCnt.deptno = emp.deptno