select v_name,sum(vjn_fenshu) as jifen
from vendorjifennode inner join vendor on vjn_vendorid = vendor.v_id
where vjn_datetime between '2010-11-1' and '2010-12-31'
group by v_name
order by sum(vjn_fenshu) desc
怎么知道某个v_name在这个查询中排第几呢.就是说我想实现的是
v_name 排名
-------------
name1 3
) as 排名
from vendorjifennode inner join vendor on vjn_vendorid = vendor.v_id
where vjn_datetime between '2010-11-1' and '2010-12-31'
group by v_name
sum(vjn_fenshu) as jifen ,
row_number()over(order by getdate())
from vendorjifennode inner join vendor on vjn_vendorid = vendor.v_id
where vjn_datetime between '2010-11-1' and '2010-12-31'
group by v_name
order by sum(vjn_fenshu) desc
select v_name,sum(vjn_fenshu) as jifen
from vendorjifennode inner join vendor on vjn_vendorid = vendor.v_id
where vjn_datetime between '2010-11-1' and '2010-12-31'
group by v_name
--order by sum(vjn_fenshu) desc
)t
但小弟忘记说,我用的是sql2000
sum(vjn_fenshu) as jifen ,
into #t
from vendorjifennode inner join vendor on vjn_vendorid = vendor.v_id
where vjn_datetime between '2010-11-1' and '2010-12-31'
group by v_name
order by sum(vjn_fenshu) descselect *,pm=(select count(*) from #t where v_name=t.v_name and jifen>=t.jifen)
from #t t
用个临时表最简单了,呵呵