【窗口函数】RANK ()

RANK() 窗口函数并列排名跳名次函数说明函数作用特点RANK()分组排名相同值同名次后续名次跳过结果1,1,3,4,4,6实战案例各部门工资前二的员工数据表Employee 员工表idnamesalarydepartment_id1Joe8500012Henry8000023San6000024Max9000015Janet6900016Randy8500017Will700001Department 部门表idname1IT2Sales建表与测试数据CREATETABLEDepartment(idINTPRIMARYKEYCOMMENT部门编号,nameVARCHAR(20)NOTNULLCOMMENT部门名称)ENGINEInnoDBDEFAULTCHARSETutf8mb4;INSERTINTODepartment(id,name)VALUES(1,IT),(2,Sales);CREATETABLEEmployee(idINTPRIMARYKEYCOMMENT员工工号,nameVARCHAR(20)NOTNULLCOMMENT员工姓名,salaryINTNOTNULLCOMMENT工资,department_idINTCOMMENT部门编号,FOREIGNKEY(department_id)REFERENCESDepartment(id))ENGINEInnoDBDEFAULTCHARSETutf8mb4;INSERTINTOEmployee(id,name,salary,department_id)VALUES(1,Joe,85000,1),(2,Henry,80000,2),(3,San,60000,2),(4,Max,90000,1),(5,Janet,69000,1),(6,Randy,85000,1),(7,Will,70000,1);题目基于两张表查询每个部门工资前二高的员工相同工资并列排名selecttem.name,d.tem.department_name,tem.salaryfrom(selecte.nameasname,d.nameasdepartment_name,e.salary,ROW_NUMBER()over(PARTITIONbydepartment_idORDERBYsalarydesc)asrank_idfromEmployee eleftjoinDepartment dond.ide.department_id)temwheretem.rank_id2运行结果