sql 排序函数

1、row_number（常用）

 不对order by处理，没有并列行号

SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY count ) AS row ,
*
FROM a
) result
WHERE result.row BETWEEN 1 AND 5;

 

 

2、rank

rank类似于row_number,不同之处在于，它会对order by 的字段进行处理，如果这个字段值相同，行号并列

SELECT  *
FROM    ( SELECT    rank() OVER ( ORDER BY count ) AS row ,
                    *
          FROM      a
        ) result
WHERE   result.row BETWEEN 1 AND 5;

　

 

 

3、dense_rank

  与rank类型，不同之处在于行号是否保留一个位置，
       ---rank对保留这个位置，两个人并列第一，接着排第三名，
       ---dense_rank不保留这个位置，两个人并列第一，接着排第二名

SELECT  *
FROM    ( SELECT    dense_rank() OVER ( ORDER BY count ) AS row ,
                    *
          FROM      a
        ) result
WHERE   result.row BETWEEN 1 AND 5;

 

 

4、ntile：为装桶操作，ntile(桶数）它在运行之前，先确定产生的桶数，然后根据桶数去生成行标

 SELECT NTILE(3) OVER ( ORDER BY count ) AS ntile ,
        *
 FROM   a;

 SELECT NTILE(2) OVER ( ORDER BY count ) AS ntile ,
        *
 FROM   a;