oracle 一个select语句的问题

[qq-dabins]

找出每个部门挣钱最多的那个人的信息.
select ename, sal from emp join (select deptno, max(sal) max_sal from emp group by deptno) t on (emp.sal=t.max_sal and emp.deptno=t.deptno);
可以执行。

但是当我想要多显示一个字段，即这些人的部门号时，却会出错，如下，只加了个deptno

select deptno, ename, sal from emp join (select deptno, max(sal) max_sal from emp group by deptno) t on (emp.sal=t.max_sal and emp.deptno=t.deptno);

请问为什么在开头加了一个deptno字段，就不能正常查询了呢？

[nalnait]

它不知道是哪一个了。你可以限定一下：

TRY

select emp.deptno, ename, sal from emp join (select deptno, max(sal) max_sal from emp group by deptno) t on (emp.sal=t.max_sal and emp.deptno=t.deptno); 

[CH_Accp]

楼上正解。

[ljhcy99]

---这样写可以的
select emp.sal,emp.deptno,emp.ename
from emp
join
(select deptno, max(sal) max_sal
from emp
group by deptno
) as t
on (emp.sal=t.max_sal and emp.deptno=t.deptno)

---测试数据
select A.sal,A.deptno,A.ename
from
(select 1 as deptno,'A' as ename,100 as sal
union
select 1 as deptno,'B' as ename,400 as sal
union
select 2 as deptno,'A' as ename,100 as sal
union
select 3 as deptno,'C' as ename,500 as sal
union
select 3 as deptno,'A' as ename,100 as sal
union
select 3 as deptno,'D' as ename,670 as sal
union
select 4 as deptno,'A' as ename,100 as sal
union
select 4 as deptno,'D' as ename,6000 as sal
) AS A join
(select MAX(sal) AS sal,deptno
from
(select 1 as deptno,'A' as ename,100 as sal
union
select 1 as deptno,'B' as ename,400 as sal
union
select 2 as deptno,'A' as ename,100 as sal
union
select 3 as deptno,'C' as ename,500 as sal
union
select 3 as deptno,'A' as ename,100 as sal
union
select 3 as deptno,'D' as ename,670 as sal
union
select 4 as deptno,'A' as ename,100 as sal
union
select 4 as deptno,'D' as ename,6000 as sal
) AS A
group by deptno
) AS AA
on (AA.deptno =A.deptno
and AA.sal = A.sal)