问题:

Hi I'm new to SQL and I'm trying to figure out how I'm going to get the top 5 "bands" with most friends (userId) and this is what i have; a usertbl with userId as PK then a bandsTbl with bandId as PK then I have a table bandfriends with FK userId and bandId.

bandfriends
userid | bandId
---------------
 1     | 1
 1     | 2
 1     | 3

Thanks!

回答1:

Read up on COUNT and GROUP BY at mysql.org

You'll want something like this (I haven't tested it):

SELECT bandId, COUNT(*) as fans FROM bandfriends
ORDER BY fans DESC
GROUP BY bandId
LIMIT 5;

回答2:

SELECT TOP 5 bandId, fanCount
FROM 
    (SELECT bandId, COUNT(*) as fanCount
     FROM bandfriends
     GROUP BY bandId
     ORDER BY COUNT(*) DESC)

You can also optionally specify WITH TIES in the select statement. See this and this.

回答3:

select top 5 b.b_name, count(friends) as numOfFriends 
from bands b inner join link l on b.b_id = l.bands inner join
friends f on f.f_id = l.friends 
group by b.b_name 
order by numOfFriends desc

If you have friends table, bands table and a link table, works for me :)