[1008]ROW_NUMBER和RANK和DENSE_RANK的区别

row_number() over(partition by order by )使用说明书

作用：数据库去除重复记录，进行排序。

语法：ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)
功能：表示根据COL1分组，在分组内部根据 COL2排序，而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的）
row_number() 返回的主要是“行”的信息，并没有排名

--在test表中根据name分组，age进行排序
select name,age,row_number() over(partition by name order by
age desc) from test;

--去掉重复的记录
select * from (select name,age,row_number() over( partition by name 
order by age desc) rn from test )where rn= 1;

ROW_NUMBER和RANK和DENSE_RANK的区别

Sql格式

SELECT *,
row_number() over(ORDER BY investment desc) as `row_number`,
dense_rank() over(order by investment desc) as `dense_rank`,
rank() over(order by investment desc) as `rank`
FROM investmenttab

• row_number 排序值从小到大,依次排列
• dense_rank 相同数据,并列保存,不存在断值(一直连续)
• rank 相同数据并列保存,下一个值跳值(断续)

• 分组获取每组前十

select aaa.* 
from 
(
SELECT
area,investment,
rank() over(PARTITION by area order by investment desc) as `rank`
from 
investmenttab tab 
left join users us
on tab.userid = us.userid
) aaa
where aaa.rank <10

参考：https://blog.youkuaiyun.com/farxix/article/details/79784608
https://blog.youkuaiyun.com/weixin_40869022/article/details/120275001