if()
使用:
if(判断条件,是的话等于什么值,不是的话等于什么值)
example:a是否等于3,是的话赋值为1,不是的话赋值为0
if(a=3,1,0)
开窗函数:
rank() over (partition by 参数一order by 参数二 desc)
example:
原表:students表
students | grade | height |
---|---|---|
zhou | 100 | 170 |
zhou | 99 | 168 |
zhou | 98 | 168 |
zhou | 98 | 167 |
zhang | 78 | 150 |
zhang | 60 | 160 |
fu | 70 | 153 |
select student,grade,rank() over (partition by students order by height desc) as sert from students;
现表:
students | grade | sert |
---|---|---|
zhou | 100 | 1 |
zhou | 99 | 2 |
zhou | 98 | 2 |
zhou | 98 | 4 |
zhang | 60 | 1 |
zhang | 78 | 2 |
fu | 70 | 1 |
row_number() over (partition by 参数一order by 参数二 desc)
example:
原表:students表
students | grade | height |
---|---|---|
zhou | 100 | 170 |
zhou | 99 | 168 |
zhou | 98 | 168 |
zhou | 98 | 167 |
zhang | 78 | 150 |
zhang | 60 | 160 |
fu | 70 | 153 |
select student,grade,row_number() over (partition by students order by height desc) as sert from students;
现表:
students | grade | sert |
---|---|---|
zhou | 100 | 1 |
zhou | 99 | 2 |
zhou | 98 | 3 |
zhou | 98 | 4 |
zhang | 60 | 1 |
zhang | 78 | 2 |
fu | 70 | 1 |
dense_rank() over (partition by 参数一order by 参数二 desc)
example:
原表:students表
students | grade | height |
---|---|---|
zhou | 100 | 170 |
zhou | 99 | 168 |
zhou | 98 | 168 |
zhou | 98 | 167 |
zhang | 78 | 150 |
zhang | 60 | 160 |
fu | 70 | 153 |
select student,grade,dense_rank() over (partition by students order by height desc) as sert from students;
现表:
students | grade | sert |
---|---|---|
zhou | 100 | 1 |
zhou | 99 | 2 |
zhou | 98 | 2 |
zhou | 98 | 3 |
zhang | 60 | 1 |
zhang | 78 | 2 |
fu | 70 | 1 |