求出每个team粉丝数最多的3个国家

有这么个表

fans(team,nationality,fanCount)

'Barcelona','Germany',12000
'Barcelona','Spain',18000
'Barcelona','China',13000
'Barcelona','USA',15000
'Barcelona','Italy',11000
'Real Madrid','France',19000
'Real Madrid','Germany',13000
'Real Madrid','USA',17000
'Real Madrid','Korea',16000
'Real Madrid','UK',12000


现在分别要求出每个team粉丝数最多的3个国家

select * 
from fans t1
where
(
  select count(*)
  from fans t2
  where t1.team=t2.team
  and t2.fanCount>=t1.fanCount
) <=3

 

posted @ 2013-08-19 21:38  残阳飞雪  阅读(245)  评论(0)  编辑  收藏  举报