Oracle分析函数与分组关键字的用法.docxVIP

Oracle分析函数与分组关键字的用法以下是我以前工作中做报表常用的几个函数，在此分享一下，希望对大家有帮助。(一)分析函数row_number?PurposeROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause, beginning with 1.You cannot use ROW_NUMBER or any other analytic function for expr. That is, you can use other built-in function expressions for expr, but you cannot nest analytic functions.按部门分组后根据工资排序，序号rn特征：连续、无并列select?t.*, row_number() over(partitionby?t.deptno?orderby?sal?desc) rn?from?emp t;?rank?PurposeRANK calculates the rank of a value in a group of values. Rows with equal values for the ranking criteria receive the same rank. Oracle then adds the number of tied rows to the tied rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers.?As an aggregate function, RANK calculates the rank of a hypothetical row identified by the arguments of the function with respect to a given sort specification. The arguments of the function must all evaluate to constant expressions within each aggregate group, because they identify a single row within each group. The constant argument expressions and the expressions in the ORDER BY clause of the aggregate match by position. Therefore, the number of arguments must be the same and their types must be compatible.?As an analytic function, RANK computes the rank of each row returned from a query with respect to the other rows returned by the query, based on the values of the value_exprs in the order_by_clause.按部门分组后根据工资排序，序号rn特征：不连续、有并列select?t.*, rank() over(partitionby?t.deptno?orderby?sal?desc) rn?from?emp t;?dense_rank?PurposeDENSE_RANK computes the rank of a row in an ordered group of rows. The ranks are consecutive integers beginning with 1. The largest rank value is the number of unique values returned by the query. Rank values are not skipped in the event of ties. Rows with equal value