资源描述:
《SQL ROW_NUMBER() OVER函数的基本用法》由会员上传分享,免费在线阅读,更多相关内容在教育资源-天天文库。
1、语法:ROW_NUMBER()OVER(PARTITIONBYCOLUMNORDERBYCOLUMN)简单的说row_number()从1开始,为每一条分组记录返回一个数字,这里的ROW_NUMBER()OVER(ORDERBYxlhDESC)是先把xlh列降序,再为降序以后的没条xlh记录返回一个序号。示例:xlhrow_num1700115002108537104row_number()OVER(PARTITIONBYCOL1ORDERBYCOL2)表示根据COL1分组,在分组内部根据COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的
2、)实例:初始化数据createtableemployee(empidint,deptidint,salarydecimal(10,2))insertintoemployeevalues(1,10,5500.00)insertintoemployeevalues(2,10,4500.00)insertintoemployeevalues(3,20,1900.00)insertintoemployeevalues(4,20,4800.00)insertintoemployeevalues(5,40,6500.00)insertintoemployeevalues(6,4
3、0,14500.00)insertintoemployeevalues(7,40,44500.00)insertintoemployeevalues(8,50,6500.00)insertintoemployeevalues(9,50,7500.00)数据显示为empiddeptidsalary-------------------------------------------------------------1105500.002104500.003201900.004204800.005406500.0064014500.0074044500.0085065
4、00.009507500.00需求:根据部门分组,显示每个部门的工资等级预期结果:empiddeptidsalaryrank---------------------------------------------------------------------------------1105500.0012104500.0024204800.0013201900.00274044500.00164014500.0025406500.0039507500.0018506500.002SQL脚本:SELECT*,Row_Number()OVER(partitionby
5、deptidORDERBYsalarydesc)rankFROMemployee语法:ROW_NUMBER()OVER(PARTITIONBYCOLUMNORDERBYCOLUMN)简单的说row_number()从1开始,为每一条分组记录返回一个数字,这里的ROW_NUMBER()OVER(ORDERBYxlhDESC)是先把xlh列降序,再为降序以后的没条xlh记录返回一个序号。示例:xlhrow_num1700115002108537104row_number()OVER(PARTITIONBYCOL1ORDERBYCOL2)表示根据COL1分组,在分组内部根
6、据COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)实例:初始化数据createtableemployee(empidint,deptidint,salarydecimal(10,2))insertintoemployeevalues(1,10,5500.00)insertintoemployeevalues(2,10,4500.00)insertintoemployeevalues(3,20,1900.00)insertintoemployeevalues(4,20,4800.00)insertintoemployeevalues(5
7、,40,6500.00)insertintoemployeevalues(6,40,14500.00)insertintoemployeevalues(7,40,44500.00)insertintoemployeevalues(8,50,6500.00)insertintoemployeevalues(9,50,7500.00)数据显示为empiddeptidsalary-------------------------------------------------------------1105500.002104500.003201900.0042048