这是使用的一种方法
user-defined variable：
select MatchDate, HomeTeam, AwayTeam, HomeGoals, AwayGoalsfrom ( 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
SQL小提琴演示
这是小提琴的更新：
select team, MatchDate, HomeTeam, AwayTeam, HomeGoals, AwayGoalsfrom ( 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 <= 6order by team
更新了SQL Fiddle
