考虑这么写
select * from salary where s_salary in (select min(s_salary) from salary group by dep )但是两个拿相同工资的人,在一个部门可能最低,在另一个部分就不是了。
select * from salary where s_salary in (select min(s_salary) from salary group by dep )但是两个拿相同工资的人,在一个部门可能最低,在另一个部分就不是了。
from emp a, (select deptno, min(sal) sal from emp group by deptno) b
where a.deptno = b.deptno
and a.sal = b.sal
select * from
(
select *, dense_rank()over (partition by deptno order by sal desc) as cn
from emp)
where cn=1
2 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
Executed in 0.031 seconds
select a.*,row_number() over (partition by a.dep order by a.salary desc) rn from s_salary a) where rn=1
SAMPLE:下面例子中dept_min返回当前行所在部门的最小薪水值
*/SELECT DEPARTMENT_ID,
LAST_NAME,
SALARY,
MIN(SALARY) OVER(PARTITION BY DEPARTMENT_ID) AS DEPT_MIN
FROM EMPLOYEES