如何在 ORACLE 中使用 ROWNUM 进行最大排序和另一个最小排序？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25481256/
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
How to use ROWNUM for a maximum and another minimum ordering in ORACLE?
提问by ps4one
Currently i am trying to output the top row for 2 condition. One is max and one is min. Current code
目前我正在尝试输出 2 个条件的顶行。一种是最大值，一种是最小值。当前代码
Select *
from (MY SELECT STATEMENT order by A desc)
where ROWNUM <= 1
UPDATEI am now able to do for both condition. But i need the A to be the highest, if same then check for the B lowest.
更新我现在可以为这两种情况做。但我需要 A 是最高的，如果相同则检查 B 最低。
E.g Lets say there is 2 rows, Both A is 100 and B is 50 for one and 60 for other. In this case the 100:50 shld be choose because A is same then B is lowest.
例如，假设有 2 行，A 均为 100，B 为 50，另一行为 60。在这种情况下，应该选择 100:50，因为 A 相同，然后 B 最低。
E.g Lets say there is 2 rows, A is 100 for one and 90 for other, since one is higher no need to check for B.
例如，假设有 2 行，A 为 100 行，另一行为 90 行，因为一行更高，无需检查 B。
I tried using max and min but this method seems to work better, any suggestions
我尝试使用 max 和 min 但这种方法似乎效果更好，有什么建议
回答by Thorsten Kettner
Well, after your clarification, you are looking for one record. With Max A. And the smallest B, in case there is more than one record with MAX A. This is simply:
好吧，在您澄清之后，您正在寻找一项记录。使用 Max A. 和最小的 B，以防 MAX A 有多个记录。这很简单：
Select *
from (MY SELECT STATEMENT order by A desc, B)
where ROWNUM = 1;
This sorts by A descending first, so you get all maximal A records first. Then it sorts by B, so inside each A group you get the least B first. This gives you the desired A record first, no matter if the found A is unique or not.
这首先按 A 降序排序，因此您首先获得所有最大的 A 记录。然后按 B 排序，因此在每个 A 组中，您首先得到最少的 B。无论找到的 A 是否唯一，这都会首先为您提供所需的 A 记录。
回答by Used_By_Already
or avoid the vagaries of rownun and go for row_number() instead:
或者避免 rownun 的变幻莫测而使用 row_number() 代替：
SELECT
*
FROM (
SELECT
*
, ROW_NUMBER (ORDER BY A DESC) adesc
, ROW_NUMBER (ORDER BY B ASC) basc
FROM SomeQuery
)
WHERE adesc = 1
OR basc = 1
footnote: select *is a convenience only, please replace with the actual columns required along with table names etc.
脚注：select *仅为方便起见，请替换为所需的实际列以及表名等。
回答by Vikas Hardia
Try this if that works
试试这个，如果有效
Select *
from (MY SELECT STATEMENT order by A desc)
where ROWNUM <= 1
union
Select *
from (MY SELECT STATEMENT order by A asc)
where ROWNUM <= 1
回答by Ortiga
SELECT * FROM
(Select foo.*, 0 as union_order
from (MY SELECT STATEMENT order by A desc) foo
where ROWNUM <= 1
UNION
Select foo.*, 1
from (MY SELECT STATEMENT order by B asc) foo
where ROWNUM <= 1)
ORDER BY
union_order
