sql统计排名

create table tb(员工姓名 varchar(10),销售金额 int,销售时间 datetime)
insert into tb values('员工A', 10 ,'2011-8-3 00:56:01')
insert into tb values('员工C', 15 ,'2011-9-1 11:56:01')
insert into tb values('员工D', 20 ,'2011-9-2 23:56:01')
insert into tb values('员工B', 10 ,'2011-9-3 11:56:01')
insert into tb values('员工C', 30 ,'2011-9-4 08:56:01')
insert into tb values('员工D', 10 ,'2011-7-6 05:56:01')
go

--sql 2000
select m.* , 名次 = (select count(1) from 
(
  select 员工姓名 , sum(销售金额) 销售总额 from tb where convert(varchar(7),销售时间,120) = '2011-09' group by 员工姓名
) n where n.销售总额 > m.销售总额) + 1 from
(
  select 员工姓名 , sum(销售金额) 销售总额 from tb where convert(varchar(7),销售时间,120) = '2011-09' group by 员工姓名
) m
order by 名次


--sql 2005
select m.* , 名次 = row_number() over(order by 销售总额 desc) from
(
  select 员工姓名 , sum(销售金额) 销售总额 from tb where convert(varchar(7),销售时间,120) = '2011-09' group by 员工姓名
) m
order by 名次

drop table tb

/*
员工姓名       销售总额        名次          
---------- ----------- ----------- 
员工C        45          1
员工D        20          2
员工B        10          3

（所影响的行数为 3 行）
*/