Row_Number() over(partition by)

declare @t as table
(
  empid int,
  depid int,
  depname varchar(10),
  salary decimal(10,2)    
)

insert into @t(empid,depid,depname,salary)
values
(101,10,'市场部',5000),
(102,10,'市场部',4500),
(103,20,'科技部',5000),
(104,20,'科技部',6000),
(105,30,'财务部',4000),
(106,30,'财务部',3000),
(107,30,'财务部',4500)

select * from @t

 

 

根据部门分组，显示每个部门的工资等级

select 
 empid,depid,depname,salary,
 ROW_NUMBER() OVER(partition by depid order by salary desc) as ParNumber,
 ROW_NUMBER() OVER( order by depid,salary desc) as RowNumber
from @t