36 lines
1023 B
HTML
36 lines
1023 B
HTML
|
<div>一、手工查询得出名次</div>
|
|||
|
|
<div>select * ,select count(*)+1 from T2 where T2.[成绩] > T1.[成绩] as 名次 from T1</div>
|
|||
|
|
<div>结果： </div>
|
|||
|
|
<div>1</div>
|
|||
|
|
<div>2</div>
|
|||
|
|
<div>3</div>
|
|||
|
|
<div>3</div>
|
|||
|
|
<div>5</div>
|
|||
|
|
<div> </div>
|
|||
|
|
<div>二、SQL Server 2005 的排序函数 ROW_NUMBER、RANK、DENSE_RANK</div>
|
|||
|
|
<div>1.ROW_NUMBER</div>
|
|||
|
|
<div>select *,ROW_NUMBER() over(partition by 分组列1，分组列2 ORDER BY 排序1，排序2) from T</div>
|
|||
|
|
<div>结果：</div>
|
|||
|
|
<div>1</div>
|
|||
|
|
<div>2</div>
|
|||
|
|
<div>3</div>
|
|||
|
|
<div>4</div>
|
|||
|
|
<div>5</div>
|
|||
|
|
<div> </div>
|
|||
|
|
<div>2.RANK</div>
|
|||
|
|
<div>select *,RANK() over(partition by 分组列1，分组列2 ORDER BY 排序1，排序2) from T</div>
|
|||
|
|
<div>结果：</div>
|
|||
|
|
<div>1</div>
|
|||
|
|
<div>2</div>
|
|||
|
|
<div>3</div>
|
|||
|
|
<div>3</div>
|
|||
|
|
<div>5</div>
|
|||
|
|
<div> </div>
|
|||
|
|
<div>3.DENSE_RANK</div>
|
|||
|
|
<div>select *,DENSE_RANK() over(partition by 分组列1，分组列2 ORDER BY 排序1，排序2) from T</div>
|
|||
|
|
<p> 结果：</p>
|
|||
|
|
<div>1</div>
|
|||
|
|
<div>2</div>
|
|||
|
|
<div>3</div>
|
|||
|
|
<div>3</div>
|
|||
|
|
<div>4</div>
|