有一表：
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, '[email protected]+ ' as state from dd where userid=3 and food not in (select food from cc) order by price desc; ')
set @[email protected]+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--限制前三名