sql Rnak() 排名函数

**

要求：图一转换为图二 ，排名函数 (sql server)

**

select  cusnb ,score ,Rank() OVER( order by score desc) rank
from table1 

**

要求（按照时间排序，计算用户两天间的分数score 差距）

**

图一

图二

思路： 按照用户进行分区，按照时间进行排名

	    select a.[name]
	    	,a.[time]  stime
			,ISNULL(b.[time],a.[time]) etime 
			,a.[score] s_score			
			,ISNULL(b.score,a.[score]) e_score
			,ISNULL(b.score,a.[score]) -a.[score] score
		from (
				select a.[name],[score],cast(a.[time] as datetime) [time]
					,RANK() over(partition by a.[name] order by a.[time] asc) rank
				from  [Table2]  a
		) a
		left join (
				select a.[name],[score],cast(a.[time] as datetime) [time]
					,RANK() over(partition by a.[name] order by a.[time] asc) rank
				from [Table2]  a
	
		) b on b.rank = a.rank + 1 and a.name = b.name