Sql进阶用法（分组排序）

rank()       over(partition by [field] order by [field] [asc/desc]) --同排名不连续    1 1 3
row_number() over(partition by [field] order by [field] [asc/desc]) --不同排名        1 2 3
dense_rank() over(partition by [field] order by [field] [asc/desc]) --同排名连续      1 1 2
count()      over(partition by [field] order by [field] [asc/desc]) --统计
first_value([field])over(partition by [field] order by [field] [asc/desc]) --取第一个值
last_value([field]) over(partition by [field] order by [field] [asc/desc]) --取第一个值
-- max min avg集合函数均可以使用开窗函数

WINDOW r AS ( PARTITION BY [field] ORDER BY [field] [asc/desc] ) 

--复用

row_number() over(r) as row_idx,

• 条件统计，开窗函数分组排序，rank=1；倒数第一，开窗函数倒排，rank=1 或count统计排名数等于总数
• 使用开窗函数first_value()over(),last_value()over()

SELECt
id,
@curRank := @curRank + 1 AS rank
FROM
person p,(SELECT @curRank := 0) r
ORDER BY
age

sum(if([condition],1,0)) --某个条件下的数量