在MySQL数据库中获取每个团队的最后6条记录 [英] Getting last 6 records for each team in a MySQL database

问题描述

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

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

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

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

我如何检索这些数据的子集，其中包含每个团队参与的最近6场比赛?

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

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

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).

推荐答案

这是使用user-defined variable做到这一点的一种方法:

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

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提琴

这篇关于在MySQL数据库中获取每个团队的最后6条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！