SQL 开窗函数

declare @t as table
(
 FCourse varchar(10),
 FScore  decimal(4,1),
 FType   varchar(10)
)

insert into @t(FCourse,FScore,FType)
values
('语文',95,'文科'),
('数学',90,'理科'),
('英语',90,'文科'),
('政治',88,'文科'),
('历史',87,'文科'),
('地理',86,'文科'),
('物理',85,'理科'),
('化学',85,'理科'),
('生物',70,'文科'),
('体育',100,'其他')

select * from @t

 

ROW_NUMBER() 

select 
 row_number() over(order by FScore desc) as FIndex,
 FCourse,
 FScore,
 FType
from @t 

 

 RANK()

select 
 rank() over(order by FScore desc) as FIndex,
 FCourse,
 FScore,
 FType
from @t

 

DENSE_RANK()

select 
 dense_rank() over(order by FScore desc) as FIndex,
 FCourse,
 FScore,
 FType
from @t

 

  ROW_NUMBER() OVER( partition by )

select 
 row_number() over(partition by FType order by FScore desc) as FIndex,
 FCourse,
 FScore,
 FType
from @t

 

select *
from (
 select 
   row_number() over(partition by FType order by FScore desc) as FIndex,
   FCourse,
   FScore,
   FType
 from @t
) as a
where FIndex=1