跪求一SQL语句!快来救命啊！排序问题！在线等.....
有一表：
userid         food         price    

3                     a               100

3                     b               1000
 
4                     a               1200

1                     b               800

4                     b               600


现在根据userid查询，比如userid=3  
想得到的查询结果是：
food       state

  a             第二

  b             第一

即想要得到某个food的排序  

不知大家看懂没呢。。。。。。

------解决方案--------------------

truncate table cc
DECLARE @aa int
DECLARE @bb int
set @aa=(select count(*) from dd where userid=3)
set @bb=1
while (@bb <= @aa )
BEGIN
execute ( 'insert into cc select top 1 userid,food,price, '+@bb+ ' as state from dd where userid=3 and food not in (select food from cc) order by price desc; ')
set @bb=@bb+1
end
select food, '第 '+convert(varchar(30),state) state from cc order by food

这个我试了,应该行
------解决方案--------------------
drop table 表
go
create table 表(userid int,food varchar(20),price int)
insert into 表
select 3, 'a ',100
union all select 20, 'a ',200
union all select 31, 'b ',1500
union all select 8, 'a ',300
union all select 3, 'b ',1000
union all select 4, 'a ',1200
union all select 1, 'b ',800
union all select 4, 'b ',600
go
select *,state=0 into #t1 from 表
go
update b
set state=(select count(*)+1 from #t1 a where a.food=b.food and a.price> b.price)
from #t1 b
go
select food, '第 '+rtrim(state) as state from #t1 where userid=3 and state <=3 --加个条件
go
drop table #t1
------解决方案--------------------
select* ,[排名]=(select '第 '+rtrim(count(*))+ '名 ' from @ta where food=a.food and price! <a.price)
from @ta a
where userid=3
and
(select count(1) from @ta where food=a.food and price! <a.price)!> 3--限制前三名