语法格式:row_number() over(partition by 分组列 order by 排序列 desc)
在使用 row_number() over()函数时候,over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。
一次排序:对查询结果进行排序(无分组)
SELECT id, name, age, salary, ROW_NUMBER() OVER(ORDER BY salary DESC) rank
FROM test_row_number_over t
结果:
进一步排序:根据id分组排序
SELECT id, name, age, salary, ROW_NUMBER() OVER(PARTITION BY id ORDER BY salary DESC) rank
FROM test_row_number_over t
结果:
再一次排序:找出每一组中序号为一的数据
SELECT * FROM(SELECT id, name, age, salary, ROW_NUMBER() OVER(PARTITION BY id ORDER BY salary DESC) rank
FROM test_row_number_over t)
WHERE rank <2
结果:
排序找出年龄在13岁到16岁数据,按salary排序
SELECT id, name, age, salary, ROW_NUMBER() OVER(ORDER BY salary DESC) rank
FROM test_row_number_over t WHERE age BETWEEN '15' AND '16'
结果:结果中 rank 的序号,其实就表明了 over(order by salary desc) 是在where age between and 后执行的