SQL interview questions

This post is intended to include some SQL interview questions I have encountered or seen online, which is equivalent to a personal record.

It will be updated continuously in the future.

 

2.3.9

There is such a table

Fans (Team, nationality, fancount)

'Barri', 'Germany ', 12000
'Barri', 'Spain ', 18000
'Barri', 'China', 13000
'Barri', 'usa', 15000
'Barri', 'italy', 11000
'Real Madrid ', 'far', 19000
'Real Madrid ', 'Germany', 13000
'Real Madrid ', 'USA', 17000
'Real Madrid ', 'korea', 16000
'Real Madrid ', 'uk', 12000

Currently, three countries with the maximum number of fans for each team are required.

 

 

Select * 
FromFans T1
Where
(
Select Count(*)
FromFans T2
WhereT1.team=T2.team
AndT2.fancount> =T1.fancount
)<=3