--测试环境
create table #aa
(
date datetime
, [name] varchar(20)
, score int
)
insert into #aa
select '2012-07-01','张三',80 union all
select '2012-07-01','李四',85 union all
select '2012-07-01','王二',70 union all
select '2012-07-01','麻子',80 union allselect '2012-07-02','李四',90 union all
select '2012-07-02','张三',85 union all
select '2012-07-02','麻子',70 union all
select '2012-07-02','王二',80 union allselect '2012-07-03','李四',60 union all
select '2012-07-03','张三',85 union all
select '2012-07-03','王二',85 union all
select '2012-07-03','麻子',70 /*
结果:
date name score place
2012-07-01 李四 85 1
2012-07-01 麻子 80 2
2012-07-01 张三 80 2
2012-07-01 王二 70 4
2012-07-02 李四 90 1
2012-07-02 张三 85 2
2012-07-02 王二 80 3
2012-07-02 麻子 70 4 2012-07-03 王二 85 1
2012-07-03 张三 85 1
2012-07-03 麻子 70 3
2012-07-03 李四 60 4
*/
select * ,row_number() over (partition by date order by date,score desc) from #aa
date name score
----------------------- -------------------- ----------- --------------------
2012-07-01 00:00:00.000 李四 85 1
2012-07-01 00:00:00.000 麻子 80 2
2012-07-01 00:00:00.000 张三 80 3
2012-07-01 00:00:00.000 王二 70 4
2012-07-02 00:00:00.000 李四 90 1
2012-07-02 00:00:00.000 张三 85 2
2012-07-02 00:00:00.000 王二 80 3
2012-07-02 00:00:00.000 麻子 70 4
2012-07-03 00:00:00.000 张三 85 1
2012-07-03 00:00:00.000 王二 85 2
2012-07-03 00:00:00.000 麻子 70 3
2012-07-03 00:00:00.000 李四 60 4
select * ,rank()over(partition by date order by date,score desc) from #aadate name score
----------------------- -------------------- ----------- --------------------
2012-07-01 00:00:00.000 李四 85 1
2012-07-01 00:00:00.000 麻子 80 2
2012-07-01 00:00:00.000 张三 80 2
2012-07-01 00:00:00.000 王二 70 4
2012-07-02 00:00:00.000 李四 90 1
2012-07-02 00:00:00.000 张三 85 2
2012-07-02 00:00:00.000 王二 80 3
2012-07-02 00:00:00.000 麻子 70 4
2012-07-03 00:00:00.000 张三 85 1
2012-07-03 00:00:00.000 王二 85 1
2012-07-03 00:00:00.000 麻子 70 3
2012-07-03 00:00:00.000 李四 60 4(12 行受影响)
--sorry