获取与Oracle中的最大和最小行有关的值 [英] Getting values relating to the max and min rows in Oracle
问题描述
在Oracle 11g中，我们需要能够查询表以从特定组中具有最高和最低值的行中提取信息.例如，使用EMP表，我们希望找到每个部门中薪水最高的人员的姓名和薪水最低的人员的姓名
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
DEPTNO MAX_SAL MAX_EARNER MIN_SAL MIN_EARNER
-------------------------------------------------------
10 5000 KING 1300 MILLER
20 3000 FORD 2975 JONES
etc
(如果有两个或更多员工的salaray最高或最低，我们希望始终按字母顺序返回第一个).
(if there are two or more staff with the highest or lowest salaray we want to always return the first in alphabetical order).
A 上一篇文章讨论了如何仅获取最大值而不获取max和min的值.
A Previous Post discussed 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
推荐答案
使用解析函数可以轻松解决.如您所见，有两名员工的年薪最高为DEPT 20；这是一个重要的细节，因为针对此类问题的一些常见解决方案缺少该信息.
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.
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>
糟糕，我错过了有关结果格式的重要细节.我的数据不符合要求的输出，因为有两名员工的薪水最高.因此，我承认这个查询有点尴尬，它为我们提供了所需的布局.员工姓名上的MIN()返回字母顺序:
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 :
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>
我不喜欢这种解决方案.大多数数据集将包含此类冲突，我们需要对其进行确认.根据一些不相关的标准来过滤结果以适合Procrustean报表布局是令人误解的.我希望报表布局可以反映整个数据集.最终，它取决于查询所服务的业务目的.而且，当然，客户永远是正确的8-)
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-)
这篇关于获取与Oracle中的最大和最小行有关的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
