mysql取最后6条数据,在MySQL数据库中获取每个团队的最后6条记录

I have a MySQL database containing soccer results and want to retrieve just a specific subset of that data.

The data consists of one table containing MatchDate, HomeTeam, AwayTeam, HomeGoals, AwayGoals

How can I retrieve a subset of this data that contains the last 6 matches that each team has been involved in?

Whilst I can do this for a single team, how do I get a single subset that contains the last 6 matches for each team in the table? (I am not worried that the subset may contain some duplicates).

解决方案

Here's one way to do it with a user-defined variable:

select MatchDate, HomeTeam, AwayTeam, HomeGoals, AwayGoals

from (

select

MatchDate, HomeTeam, AwayTeam, HomeGoals, AwayGoals,

@teamCounter:=IF(@prevHome=HomeTeam,@teamCounter+1,1) teamCounter,

@prevHome:=HomeTeam

from yourtable

join (select @teamCounter:=0) t

order by HomeTeam, MatchDate desc

) t

where teamCounter <= 6

And here is the update from the Fiddle:

select team, MatchDate, HomeTeam, AwayTeam, HomeGoals, AwayGoals

from (

select

team, yourtable.MatchDate, HomeTeam, AwayTeam, HomeGoals, AwayGoals,

@teamCounter:=IF(@prevHome=team,@teamCounter+1,1) teamCounter,

@prevHome:=team

from yourtable

join (

select distinct matchdate, hometeam team

from yourtable

union

select distinct matchdate, awayteam

from yourtable

) allgames on yourtable.matchdate = allgames.matchdate

and (yourtable.hometeam = allgames.team or yourtable.awayteam = allgames.team)

join (select @teamCounter:=0) t

order by team, yourtable.MatchDate desc

) t

where teamCounter <= 6

order by team