id hits
331 100
3 99
6663 65
... ...id 是文章id
hits 是点击率怎么可以得到 根据点击率的排名
id hits pm
331 100 1
3 99 2
6663 65 3
... ... ...
331 100
3 99
6663 65
... ...id 是文章id
hits 是点击率怎么可以得到 根据点击率的排名
id hits pm
331 100 1
3 99 2
6663 65 3
... ... ...
go
create table #temp( [id] varchar(100), [hits] INT);
insert #temp
select '331','100' union all
select '3','99' union all
select '6663','65' --SQL:
select *,pm=ROW_NUMBER() OVER(ORDER BY hits DESC) from #temp
/*
id hits pm
331 100 1
3 99 2
6663 65 3
*/
select *,pm=(SELECT COUNT(*) FROM #temp b WHERE b.hits>=a.hits)
from #temp a
ORDER BY pm
/*
id hits pm
331 100 1
3 99 2
6663 65 3
*/
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
go
create table #temp( [id] varchar(100), [hits] INT);
insert #temp
select '331','100' union all
select '3','99' union all
select '6663','65'
--SQL:
select *,pm=DENSE_RANK() OVER(ORDER BY hits DESC) from #temp