急求 销售排名的问题 ---在线等
如何计算得出一个人的销售排名呢？？

一个简单表    
userid     销售额   店ID


如何按销售额排名呢？
要求   实现如下
userid     销售额     总排名     店内排名    



------解决方案--------------------
/*测试数据*/
create table PaiM(
userid int,
sales int,
DID int
)

insert into PaiM select 1,100,2
union all select 1,200,2
union all select 2,300,2
union all select 2,500,2
union all select 3,300,1
union all select 3,100,1
union all select 4,300,1

/*语句*/
select * into #temp from (select userid,sale_sum=sum(sales),DID from PaiM a group by userid,DID) t

select userid,sale_sum, '总排名 '=(select count(1)+1 from #temp where sale_sum> a.sale_sum),Did,
'店排名 '=(select count(1)+1 from #temp where sale_sum> a.sale_sum and DID=a.DID)
from #temp a order by a.sale_sum desc
------解决方案--------------------
create table #a(userid int,销售额 int,店ID int)--借用临时表处理
insert #a select userid,sum(销售额),店ID from 表名 group by userid,店ID
select userid,销售额,(select count(*)+1 from #a where b.销售额 <销售额) 总排名,
(select count(*)+1 from #a where b.店ID=店ID and b.销售额 <销售额) 店内排名
from #a b
------解决方案--------------------

create table #t(
userid int,
sales int,
DID int
)

insert into #t select 1,100,2
union all select 1,200,2
union all select 2,300,2
union all select 2,500,2
union all select 3,300,1
union all select 3,100,1
union all select 4,300,1
union all select 4,800,1


select *, '總排名 '=identity(int,1,1) into #t1 from
(
select a.userid,a.DID,b.sales
from #t a
join
(
select userid,max(sales)as sales from #t a group by userid
)b on a.userid=b.userid and a.sales=b.sales
)a
order by sales desc

select * , '店排名 '=(select count(*) from #t1 where DID=a.DID and sales <=a.sales)from #t1 a


userid DID sales 總排名 店排名
----------- ----------- ----------- ----------- -----------
4 1 800 1 2
2 2 500 2 2
3 1 300 3 1
1 2 200 4 1

(4 row(s) affected)