一、记录去重
1、distinct,放在查询字段之前,可后面多个字段
select distinct name,age from user
2、row_number() over(),可根去多个字段分组排序
select user.*, row_number() over(partition by name,age order by name desc) rn
from user
源数据
name | age | sex |
jack | 18 | 男 |
jack | 18 | 女 |
查询结果
name | age | sex | rn |
jack | 18 | 1 | |
jack | 18 | 2 |
后面会多了个行号,我们可以根据行号去重
select * from (select user.*, row_number() over(partition by name,age order by name desc) rn
from user) a
where rn =1
结果就是下面的了
name | age | sex | rn |
jack | 18 | 1 |