Oracle SQL (Oracle 11g) 中的排名优于分区依据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41453529/
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
Rank Over Partition By in Oracle SQL (Oracle 11g)
提问by Jude92
I have 4 columns in a table
我在一个表中有 4 列
- Company Part Number
- Manufacturer Part Number
- Order Number
Part Receipt Date
Ex.
- 公司零件号
- 制造商零件编号
- 订单号
零件接收日期
前任。
I just want to return one record based on the maximum Part Receipt Date which would be the first row in the table (The one with Part Receipt date 03/31/2015).
我只想根据最大零件收据日期返回一条记录，这将是表中的第一行（零件收货日期为 03/31/2015 的记录）。
I tried
我试过
RANK() OVER (PARTITION BY Company Part Number,Manufacturer Part Number
ORDER BY Part Receipt Date DESC,Order Number DESC) = 1
at the end of the WHERE statement and this did not work.
在 WHERE 语句的末尾，这不起作用。
采纳答案by mathguy
Analytic functions like rank()are available in the SELECTclause, they can't be invoked directly in a WHEREclause. To use rank()the way you want it, you must declare it in a subquery and then use it in the WHEREclause in the outer query. Something like this:
子句rank()中可用的分析函数SELECT，它们不能在WHERE子句中直接调用。要rank()以您想要的方式使用它，您必须在子查询中声明它，然后WHERE在外部查询的子句中使用它。像这样的东西：
select company_part_number, manufacturer_part_number, order_number, part_receipt_date
from ( select t.*, rank() over (partition by... order by...) as rnk
from your_table t
)
where rnk = 1
Note also that you can't have a column name like company part number(with spaces in it) - at least not unless they are enclosed in double-quotes, which is a very poor practice, best avoided.
还要注意，你不能有像company part number（里面有空格）这样的列名——至少除非它们用双引号括起来，这是一种非常糟糕的做法，最好避免。
回答by Gordon Linoff
This would seem to do what you want:
这似乎可以满足您的需求：
select t.*
from (select t.*
from t
order by partreceiptdate desc
) t
where rownum = 1;
