mysql自动排序函数dense_rank() over()、rank() over()、row_num() over()。使用函数时可能会报错：SQL错误(1064) : You have ....

一、作用区别

• rank()：是并列排序，会跳过重复序号
• dense_rank()：是并列排序，不会跳过重复序号
• row_number()：是顺序排序，不跳过任何一个序号，就是行号

二、实例效果

1、数据库数据：

mysql> select * from students;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan |   100 |
|  2 | lisi     |    99 |
|  3 | wangwu   |   100 |
|  4 | trx      |    90 |
|  5 | pjf      |    99 |
|  6 | wzm      |    96 |
+----+----------+-------+

 2、使用三种不同的方法进行排序

（1）rank()

select 
    id, 
    name, 
    rank() over(order by score desc) `rank`
from students;
 
----------------- 结果 ------------------
+----+----------+-------+------+
| id | name     | score | rank | 
+----+----------+-------+------+
|  1 | zhangsan |   100 |    1 |      
|  3 | wangwu   |   100 |    1 |         
|  2 | lisi     |    99 |    3 |         
|  5 | pjf      |    99 |    3 |         
|  6 | wzm      |    96 |    5 |         
|  4 | trx      |    90 |    6 |      
+----+----------+-------+------+

（2） dense_rank()

select 
    id, 
    name, 
    row_number() over(order by score desc) `row_number`
 
        --------------- 结果 ----------------
        +----------+-------+------------+----
        | id | name     | score | row_number|
        +----+----------+-------+-----------+
        |  1 | zhangsan |   100 |     1     |
        |  3 | wangwu   |   100 |     2     |
        |  2 | lisi     |    99 |     3     |
        |  5 | pjf      |    99 |     4     |
        |  6 | wzm      |    96 |     5     |
        |  4 | trx      |    90 |     6     |
        +----+----------+-------+-----------+

 （3）row_number()

    select
            id,
            name,
            dense_rank() over(order by score desc) `dense_rank`
        from students;

        ---------------- 结果 ----------------
        +----+----------+-------+------------+
        | id | name     | score | dense_rank |
        +----+----------+-------+------------+
        |  1 | zhangsan |   100 |      1     |
        |  3 | wangwu   |   100 |      1     |
        |  2 | lisi     |    99 |      2     |
        |  5 | pjf      |    99 |      2     |
        |  6 | wzm      |    96 |      3     |
        |  4 | trx      |    90 |      4     |
        +----+----------+-------+------------+

三、在使用 dense_rank() over()、rank() over()、row_num() over() 三个函数时， SQL错误(1064) : You have an error in your sQLsyntax; check the manual that corresponds toyour MySQL server version for the right syntax touse near "(order by salary ) 'dense_rank'
FROM employee' at line 1。

大概率原因:数据库版本过低，版本不支持用窗口函数。