求一个SQL查询语句

[chase124351]

一道笔试的试题
用hr用户登录 在employees和departments这两个表中查询员工人数最少的部门和具体人数
关键是这两个表中没有员工人数的列啊 应该是用 count吧 具体的 谁给解答下 谢了

[gelyon]

select deptno,count(1) 员工人数 from employees group by deptno;

[chase124351]

结果只是查询了每个部门的人数啊 那个题的意思是只查询一条数据 就是员工人数最少的部门名称和具体人数

[Phoenix_99]

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

[minitoy]

SQL> select b.deptno,count(a.ename)
  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> 

[gelyon]

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;

[minitoy]

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  ;
 
DEPTNO DNAME                 CNT
------ -------------- ----------
    40 OPERATIONS              0
 
SQL> 

[gelyon]

minitoy才是正确的，未考虑到没有员工的部门

[心中的彩虹]

[Quote=引用楼主 chase124351 的回复:]
一道笔试的试题
用hr用户登录 在employees和departments这两个表中查询员工人数最少的部门和具体人数
关键是这两个表中没有员工人数的列啊 应该是用 count吧 具体的 谁给解答下 谢了
[/Quote]

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

[yowbell]

全都是高手啊

[czn116688]

学习中......

[ojuju10]

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)