MySQL中的dense_rank()函数

Question

为什么下面的MySQL代码不能工作--特别是dense_rank()函数，因为“employee_number”总是以两个字母开头，例如。“AB61026”、'AB64238'等。

Select Employee, Cost_Center, Cost_Grant, Percent
,DENSE_RANK() over (PARTITION BY Employee order by Percent ASC) as Rank

Answer 1

使用from子句很好

drop table if exists t;
create table t
(employee_number int,cost_center int,cost_grant int,percent int);

insert into t values
(1,1,1,1),
(1,1,1,1),
(1,1,1,2),
(2,1,1,3)

;


Select Employee_Number, Cost_Center, Cost_Grant, Percent
,DENSE_RANK() over (PARTITION BY Employee_Number order by Percent ASC) as Rank
from t;

+-----------------+-------------+------------+---------+------+
| Employee_Number | Cost_Center | Cost_Grant | Percent | Rank |
+-----------------+-------------+------------+---------+------+
|               1 |           1 |          1 |       1 |    1 |
|               1 |           1 |          1 |       1 |    1 |
|               1 |           1 |          1 |       2 |    2 |
|               2 |           1 |          1 |       3 |    1 |
+-----------------+-------------+------------+---------+------+
4 rows in set (0.002 sec)