数据库-sql-面试-rank

表名为 A：
ID SCORE
1 28
2 33
3 33
4 89
5 99
6 68
7 68
8 78
9 88
10 90
得到结果：
ID SCORE RANK
5 99 1
10 90 2
4 89 3
9 88 4
8 78 5
6 68 6
7 68 7
2 33 8
3 33 9
1 28 10
注意 select中是这样命名变量的 := 也可以set 用=

#使用变量方式
SELECT id,
       score,
       rank
  FROM (SELECT tmp.id,
               tmp.score,
               @rank := @rank + 1 AS rank
          FROM (SELECT id,score
                  FROM a
                 ORDER BY score desc) tmp, (SELECT @rank   := 0) a) RESULT;  

 desc user_order;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 REGION_ID                                          NUMBER(2)
 CUSTOMER_ID                                  NUMBER(2)
 CUSTOMER_SALES                          NUMBER

 select * from user_order order by customer_sales;

 REGION_ID CUSTOMER_ID CUSTOMER_SALES
---------- ----------- --------------
         5           1              151162
        10          29             903383
         6           7              971585
        10          28            986964
         9          21           1020541
         9          22           1036146
         8          16           1068467
         6           8            1141638
         5           3            1161286
         5           5            1169926
         8          19           1174421
         7          12           1182275
         7          11           1190421
         6          10           1196748
         6           9            1208959
        10          30          1216858
         5             2                1224992
           9             24              1224992
           9             23              1224992
           8          18           1253840
         7          15           1255591
         7          13           1310434
        10          27          1322747
         8          20           1413722
         6           6            1788836
        10          26          1808949
         5           4            1878275
         7          14           1929774
         8          17           1944281
         9          25           2232703

select rownum, t.*
     from (select *
              from user_order
            order by customer_sales desc) t
  where rownum <= 12
   order by customer_sales desc;

    ROWNUM  REGION_ID CUSTOMER_ID CUSTOMER_SALES
---------- ---------- ----------- --------------
         1          9                 25        2232703
         2          8                 17        1944281
         3          7                 14        1929774
         4          5                   4        1878275
         5         10                26        1808949
         6          6                   6        1788836
         7          8                 20        1413722
         8         10                27        1322747
         9          7                13        1310434
        10          7               15        1255591
        11          8               18        1253840
          12             5                     2          1224992

漏掉了另外两条记录
①ROW_NUMBER：
Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。
②DENSE_RANK：
Dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。
③RANK：
Rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
使用Oracle函数

select region_id, customer_id, sum(customer_sales) total,
        rank() over(order by sum(customer_sales) desc) rank,
         dense_rank() over(order by sum(customer_sales) desc) dense_rank,
        row_number() over(order by sum(customer_sales) desc) row_number
     from user_order
    group by region_id, customer_id;

①假如客户就只需要指定数目的记录，那么采用row_number是最简单的，但有漏掉的记录的危险

②假如客户需要所有达到排名水平的记录，那么采用rank或dense_rank是不错的选择。至于选择哪一种则看客户的需要，选择dense_rank或得到最大的记录

上面的排名是按订单总额来进行排列的，现在跟进一步：假如是为各个地区的订单总额进行排名呢？这意味着又多了一次分组操作：对记录按地区分组然后进行排名。幸亏Oracle也提供了这样的支持，我们所要做的仅仅是在over函数中order by的前面增加一个分组子句：partition by region_id

select region_id, customer_id,
               sum(customer_sales) total,
         rank() over(partition by region_id
                        order by sum(customer_sales) desc) rank,
         dense_rank() over(partition by region_id
                        order by sum(customer_sales) desc) dense_rank,
         row_number() over(partition by region_id
                        order by sum(customer_sales) desc) row_number

    from user_order
   group by region_id, customer_id;

排名将是基于各个地区的，而非所有区域的了，Partition by 子句在排列函数中的作用是将一个结果集划分成几个部分，这样排列函数就能够应用于这各个子集。