oracle生成顺序编号,Oracle排序以及序号的输出 | 学步园

在一般Select语句中我们通过Order by ...Asc/Desc来进行排序。但是这种排序方式在输出时，不包含排序后的序号信息。

现在介绍一下Oracle中可以用于排序输出的方法。

1. RANK ( ) OVER ( [query_partition_clause] order_by_clause )

RANK ( ) OVER ( [query_partition_clause] order_by_clause )中[query_partition_clause]表示Oracle中分组方法，通常语句格式为：partition by 列名1[,列名2...]

order_by_clause 表示排序的依据，通常语句格式为Order by 列名1[,列名2...]。这与Select语句中Order by 语句相同。一样可以在列名后添加Asc(默认)或Desc进行升序和降序的选择。

采用RANK ( ) OVER ( [query_partition_clause] order_by_clause )后序号的格式如下例:1,2,2,4,5,5,5,8,9,10...

RANK ( ) OVER ( [query_partition_clause] order_by_clause )对于查询出语句也会进行自动排序。

例：表ScoreTb数据如下

GroupID                   PLAYNAME                  SCORE

----------  ------------------ -------------------- ----------

01                           a1                          100

01                           a2                          100

01                           b3                           99

01                           b4                           98

01                           a5                          103

02                           a6                          101

02                           b7                           99

02                           b8                           98

02                           a9                          101

02                           aa                          101

在执行select rank() Over (partition by GroupID order by Score Desc) RK, T.* from ScoreTb T;查询结果如下：

RK       GroupID                   PLAYNAME                  SCORE

-------------------------------------------------------------------------

1           01                           a5                          103

2           01                           a1                          100

2           01                           a2                          100

4           01                           b3                           99

5           01                           b4                           98

1           02                           a6                          101

1           02                           a9                          101

1           02                           aa                          101

4           02                           b7                           99

5           02                           b8                           98

2. dense_rank() OVER ( [query_partition_clause] order_by_clause ) 语法使用上与RANK ( ) OVER ( [query_partition_clause] order_by_clause )相同，只是排序结果为：1,2,2,2,3,4,5,5,6,...

3. row_number() OVER ( [query_partition_clause] order_by_clause )语法使用上与RANK ( ) OVER ( [query_partition_clause] order_by_clause )相同，只是排序结果为：1，2，3，4，5，6...