oracle 排序函数oracle sql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14805807/
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 function oracle sql
提问by Marshall Black
I am new to sql and working on assignment. There is a question, where I am supposed to use rank function, but I have no idea how to use that. I tried to find some information on the internet, but still having problems. I am posting question here, if you have any articles or comments which would be helpful, please post here.
我是 sql 的新手，正在做作业。有一个问题，我应该在哪里使用 rank 函数，但我不知道如何使用它。我试图在互联网上找到一些信息，但仍然有问题。我在这里发布问题，如果您有任何有用的文章或评论，请在这里发布。
Thanks,
谢谢，
采纳答案by APC
When we use rank()ordered by descending POST_DATE the most recent date has the rank of 1:
当我们使用rank()POST_DATE 降序排序时，最近日期的等级为 1：
select aid
, email
, trunc((rnk-1)/5) + 1 as pageno
from ( select aid
, email
, rank() over (order by post_date desc ) as rnk
from ads )
You may need to adjust the calculation of page number; the question is somewhat ambiguous on that score ("the page number where the ad would be listed for all ads that would be listed in a page other than the first page ").
您可能需要调整页码的计算；这个问题在这个分数上有点含糊不清（“对于将在第一页以外的页面中列出的所有广告，将列出广告的页码”）。
As @AndriyM comments, in real life we would use ROW_NUMBER() for this rather than RANK() because that function would produce a distinct number for each row without gaps, which neither RANK() nor DENSE_RANK can guarantee. That would look like
正如@AndriyM 评论的那样，在现实生活中，我们会为此使用 ROW_NUMBER() 而不是 RANK() 因为该函数将为每一行生成一个不同的数字而没有间隙，RANK() 和 DENSE_RANK 都不能保证。那看起来像
row_number() over ( order by post_date desc, aid asc ) as rn
Please bear in mind that your teacher is probably smart enough to know about StackOverflow, so be honest about assigning credit where it's due.
请记住，您的老师可能足够聪明，知道 StackOverflow，所以要诚实地分配应得的学分。
回答by Angelina
The syntax for rank function :
秩函数的语法：
rank( expression1, ... expression_n ) WITHIN GROUP ( ORDER BY expression1, ... expression_n )
and this is an example :
这是一个例子：
select rank(1000, 500) WITHIN GROUP (ORDER BY salary, bonus) from employees;
