Oracle 数据库的一道SQL语句题

来源：百度知道 编辑：UC知道 时间：2024/06/14 21:17:07
写出如下SQL语句，系统用户scott 的emp和dept表,查询出员工名字以A打头的人数最多的部门名称和员工名字,
emp 表:
员工号 员工名 部门号
EMPNO ENAME DEPTNO
7369 SMITH 20
7499 ALLEN 30
7900 JAMES 10
……
dept 表:
部门号 部门名称
DEPTNO DNAME
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS

谢谢
11楼的答案截取的有点不太理解，
我自己做的现在只是能查出A打头的员工和部门~但是怎么比较最多的不知道怎么写~在这个的基础上
select e.ename,d.dname from emp e,dept d
where e.deptno=d.deptno and e.ename in (select ename from emp where ename like 'A%');

select y.dname,y.ename from

(select deptno,max(cnt) from
(select deptno,count(*) cnt where substr(trim(ename),1,1)='A' group by deptno)) x,

(select a.deptno,a.dname,b.ename from dept a,emp b where a.deptno=b.deptno and substr(trim(b.ename),1,1)='A') y

where x.deptno=y.deptno;

select e.ename, d.dname
from (select t1.dname
from (select d.dname, count(d.dname) count_num
from emp e, dept d
where e.deptno = d.deptno
and e.ename like 'A%'
group by d.dname) t1,
(select d.dname, count(d.dname) count_num
from emp e, dept d
where e.deptno = d.deptno
and e.ename like 'A%'
group by d.dname) t2
where t1.count_num > t2.count_num) t,
emp e,
dept d
where e.d