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

Question

我有一个包含足球比赛结果的MySQL数据库，并且只想检索该数据的特定子集。

数据由一个包含MatchDate，HomeTeam，AwayTeam，HomeGoals，AwayGoals的表组成

如何检索包含每个团队参与的最近6场比赛的数据的子集？

虽然我可以为单个团队执行此操作，但是如何获得一个包含表中每个团队的最后6个匹配项的子集？（我不担心该子集可能包含一些重复项）。

Answer 1

这是使用的一种方法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

SQL小提琴演示

这是小提琴的更新：

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

更新了SQL Fiddle