在 Oracle 中获取与最大和最小行相关的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5880708/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting values relating to the max and min rows in Oracle
提问by Richard Craggs
In Oracle 11g we need to be able to query a table to pull out information from rows with the highest and lowest values in a certain group. For example using the EMP table we'd like to find the name of person with the highest salary and the name of the person with the lowest salary in each department
在 Oracle 11g 中，我们需要能够查询表以从特定组中具有最高和最低值的行中提取信息。例如使用 EMP 表，我们想找到每个部门中薪水最高的人的姓名和薪水最低的人的姓名
DEPTNO MAX_SAL MAX_EARNER MIN_SAL MIN_EARNER
-------------------------------------------------------
10 5000 KING 1300 MILLER
20 3000 FORD 2975 JONES
etc
(if there are two or more staff with the highest or lowest salaray we want to always return the first in alphabetical order).
（如果有两个或更多员工的最高或最低工资，我们希望总是按字母顺序返回第一个）。
A Previous Postdiscussed how to get a value for just the maximum but not both max and min.
一个一篇文章讨论了如何获取值只是最大但不能同时最大值和最小值。
We have an untidy solution at the moment based on the link above then applying subsiquent queries but performance is important to us. I predict a good solution will also require analytic functions and possibly a pivot to coalesce the multiple rows into single rows.
我们目前基于上面的链接有一个不整洁的解决方案，然后应用后续查询，但性能对我们很重要。我预测一个好的解决方案还需要分析函数，并且可能需要一个将多行合并为单行的支点。
Any help greatly appreciated! Richard
非常感谢任何帮助！理查德
采纳答案by APC
This is easily solvable with analytic functions. As you can see, there are two employees earning the maximum salary in DEPT 20; this is an important detail, as some common solutions to this kind of problem miss that information.
这很容易用解析函数解决。如您所见，DEPT 20 中有两名员工的工资最高；这是一个重要的细节，因为此类问题的一些常见解决方案会忽略该信息。
SQL> select ename
2 , deptno
3 , sal
4 from (
5 select ename
6 , deptno
7 , sal
8 , max (sal) over (partition by deptno) max_sal
9 , min (sal) over (partition by deptno) min_sal
10 from emp
11 )
12 where sal = max_sal
13 or sal = min_sal
14 order by deptno, sal
15 /
ENAME DEPTNO SAL
---------- ---------- ----------
KISHORE 10 1300
SCHNEIDER 10 5000
CLARKE 20 800
RIGBY 20 3000
GASPAROTTO 20 3000
HALL 30 950
LIRA 30 3750
TRICHLER 50 3500
FEUERSTEIN 50 4500
9 rows selected.
SQL>
Oops, I missed an important detail about the result format. My data won't fit the requested output, because there are two employees earning the maximum salary. So this query, which I admit is a bit awkward, gives us the required layout. The MIN() on the employee names returns the alphabetical order :
糟糕，我错过了一个关于结果格式的重要细节。我的数据不符合要求的输出，因为有两名员工的工资最高。所以这个查询，我承认有点尴尬，给了我们所需的布局。员工姓名上的 MIN() 返回字母顺序：
SQL> select
2 deptno
3 , max (case when sal = min_sal then min_sal else null end ) as min_sal
4 , min (case when sal = min_sal then ename else null end ) as min_name
5 , max (case when sal = max_sal then max_sal else null end ) as max_sal
6 , min (case when sal = max_sal then ename else null end ) as max_name
7 from (
8 select ename
9 , deptno
10 , sal
11 , max (sal) over (partition by deptno) max_sal
12 , min (sal) over (partition by deptno) min_sal
13 from emp
14 )
15 where sal = max_sal
16 or sal = min_sal
17 group by deptno
18 order by deptno
19 /
DEPTNO MIN_SAL MIN_NAME MAX_SAL MAX_NAME
---------- ---------- ---------- ---------- ----------
10 1300 KISHORE 5000 SCHNEIDER
20 800 CLARKE 3000 GASPAROTTO
30 950 HALL 3750 LIRA
50 3500 TRICHLER 4500 FEUERSTEIN
SQL>
I don't like this solution. Most datasets will contain such clashes, and we need to acknowledge them. Filtering the result on the basis of some unrelated criteria to fit a Procrustean report layout is misleading. I would prefer a report layout which reflected the whole dataset. Ultimately it depends on the business purpose which the query serves. And, of course, the customer is always right 8-)
我不喜欢这个解决方案。大多数数据集都会包含这样的冲突，我们需要承认它们。根据一些不相关的标准过滤结果以适应 Procrustea 报告布局是一种误导。我更喜欢反映整个数据集的报告布局。最终，它取决于查询服务的业务目的。而且，当然，客户永远是对的 8-)
回答by Somu
You can use the below query
您可以使用以下查询
SELECT
dept,
max_sal,
(SELECT emp_name FROM emp WHERE salary = max_sal AND rownum =1) max_earner,
min_sal,
(SELECT emp_name FROM emp WHERE salary = min_sal AND rownum =1) min_earner
FROM
(SELECT
dept,
MAX(salary) max_sal,
MIN(salary) min_sal
FROM emp
GROUP BY dept);
assuming your table looks like this:
假设您的表如下所示：
CREATE TABLE emp
(
dept NUMBER,
emp_name VARCHAR2(20 BYTE),
salary NUMBER
);
Update
更新
In order to acheive your other requirement "if there are two or more staff with the highest or lowest salaray we want to always return the first in alphabetical order", you may tweak the query a bit as below (I'm sure there's scope for improvement here):
为了满足您的其他要求“如果有两个或更多员工的最高或最低工资，我们希望始终按字母顺序返回第一个”，您可以按如下方式稍微调整查询（我确定有范围此处改进）：
SELECT
dept,
max_sal,
(select emp_name from (SELECT * FROM emp order by emp_name asc) WHERE salary = max_sal AND rownum =1) max_earner,
min_sal,
(select emp_name from (SELECT * FROM emp order by emp_name asc) WHERE salary = min_sal AND rownum =1) min_earner
FROM
(SELECT
dept,
MAX(salary) max_sal,
MIN(salary) min_sal
FROM emp
GROUP BY dept);
