select deptno,count(1) 员工人数 from employees group by deptno;
select rn,department_name from( select rn,department_id,dense_rank() over(order by rn) k from( select count(1) rn,s.department_id from employees s group by s.department_id ) ) es,departments d where es.k = 1 and d.department_id = es.department_id
SQL> select b.deptno,count(a.ename) 2 from emp a,dept b 3 where b.deptno=a.deptno(+) 4 group by b.deptno;
SQL> SQL> select deptno, cnt 2 from (select b.deptno, 3 count(a.ename) cnt, 4 row_number() over(order by count(a.ename) asc) rn 5 from emp a, dept b 6 where b.deptno = a.deptno(+) 7 group by b.deptno) 8 where rn=1 9 ;
DEPTNO CNT ------ ---------- 40 0
SQL>
select deptno, cnt 员工人数 from( select deptno, cnt,rownum rn from( select deptno,count(1) cnt from emp group by deptno order by count(1) ) ) where rn=1;
SQL> select deptno,dname, cnt 2 from (select b.deptno,b.dname, 3 count(a.ename) cnt, 4 row_number() over(order by count(a.ename) asc) rn 5 from emp a, dept b 6 where b.deptno = a.deptno(+) 7 group by b.deptno,b.dname) 8 where rn=1 9 ;
select a.* from (select b.deptno,b.deptname,count(a.empno) over(partition by b.deptno) cn from employees a,departments b where a.deptno=b.deptno order by count(a.empno) over(partition by b.deptno)) a where rownum=1
select b.dept_name,count(1) from employees a, departments b where a.dept_id=b.dept_id group by b.dept_id,b.dept_name having count(1)=(select min(count(1)) from employees group by dept_id)
select rn,department_name from(
select rn,department_id,dense_rank() over(order by rn) k from(
select count(1) rn,s.department_id from employees s group by s.department_id
)
) es,departments d where es.k = 1 and d.department_id = es.department_id
2 from emp a,dept b
3 where b.deptno=a.deptno(+)
4 group by b.deptno;
DEPTNO COUNT(A.ENAME)
------ --------------
10 3
20 5
30 6
40 0
SQL>
SQL> select deptno, cnt
2 from (select b.deptno,
3 count(a.ename) cnt,
4 row_number() over(order by count(a.ename) asc) rn
5 from emp a, dept b
6 where b.deptno = a.deptno(+)
7 group by b.deptno)
8 where rn=1
9 ;
DEPTNO CNT
------ ----------
40 0
SQL>
select deptno, cnt 员工人数 from(
select deptno, cnt,rownum rn from(
select deptno,count(1) cnt from emp group by deptno order by count(1)
)
) where rn=1;
2 from (select b.deptno,b.dname,
3 count(a.ename) cnt,
4 row_number() over(order by count(a.ename) asc) rn
5 from emp a, dept b
6 where b.deptno = a.deptno(+)
7 group by b.deptno,b.dname)
8 where rn=1
9 ;
DEPTNO DNAME CNT
------ -------------- ----------
40 OPERATIONS 0
SQL>
select a.*
from (select b.deptno,b.deptname,count(a.empno) over(partition by b.deptno) cn
from employees a,departments b
where a.deptno=b.deptno
order by count(a.empno) over(partition by b.deptno)) a
where rownum=1
select b.dept_name,count(1) from employees a, departments b
where a.dept_id=b.dept_id
group by b.dept_id,b.dept_name
having count(1)=(select min(count(1)) from employees group by dept_id)