如何从表格中找到所有其他列的列的最小值和最大值

Question

如何获得最小（薪水）和最高（薪水）以及所有其他列，例如oracle SQL查询中的名称，DOB，电话号码...等。

我已经尝试了以下操作及其正常工作，但是是否有其他方法使用分析功能或类似功能。

SELECT
    a.*
FROM
         employees a
    JOIN (
        SELECT
            MIN(salary) min_sal,
            department_id
        FROM
            employees
        GROUP BY
            department_id
    ) b ON a.salary = min_sal
           AND a.department_id = b.department_id
UNION
SELECT
    a.*
FROM
         employees a
    JOIN (
        SELECT
            MAX(salary) max_sal,
            department_id
        FROM
            employees
        GROUP BY
            department_id
    ) b ON a.salary = max_sal
           AND a.department_id = b.department_id;

Answer 1

我假设这样的东西（基于Scott的样本EMP表）;查询每个部门的最低薪水和最高工资（这就是条款说的分区）：

SQL> select deptno, empno, ename, job, sal,
  2    min(sal) over (partition by deptno) min_sal,
  3    max(sal) over (partition by deptno) max_sal
  4  from emp
  5  order by deptno, sal;

    DEPTNO      EMPNO ENAME      JOB              SAL    MIN_SAL    MAX_SAL
---------- ---------- ---------- --------- ---------- ---------- ----------
        10       7934 MILLER     CLERK           1495       1495       5750
        10       7782 CLARK      MANAGER         2818       1495       5750
        10       7839 KING       PRESIDENT       5750       1495       5750
        20       7369 SMITH      CLERK            920        920       3450
        20       7876 ADAMS      CLERK           1265        920       3450
        20       7566 JONES      MANAGER         3421        920       3450
        20       7788 SCOTT      ANALYST         3450        920       3450
        20       7902 FORD       ANALYST         3450        920       3450
        30       7900 JAMES      CLERK            998        998       2993
        30       7654 MARTIN     SALESMAN        1313        998       2993
        30       7521 WARD       SALESMAN        1313        998       2993
        30       7844 TURNER     SALESMAN        1575        998       2993
        30       7499 ALLEN      SALESMAN        1680        998       2993
        30       7698 BLAKE      MANAGER         2993        998       2993

14 rows selected.

SQL>