mysql - 选择具有相同值的行，每个行都有限制

Question

嗨，我在尝试根据最近 4 场比赛的结果生成足球表时遇到问题这是我用来根据所有结果生成表格的代码，它工作正常:

select     team,     count(*) played,     count(case when goalsfor > goalsagainst then 1 end) wins,     count(case when goalsagainst> goalsfor then 1 end) lost,     count(case when goalsfor = goalsagainst then 1 end) draws,     sum(goalsfor) goalsfor,     sum(goalsagainst) goalsagainst,     sum(goalsfor) - sum(goalsagainst) goal_diff,    sum(          case when goalsfor > goalsagainst then 3 else 0 end         + case when goalsfor = goalsagainst then 1 else 0 end    ) score from (    select hometeam team, goalsfor, goalsagainst from scores   union all    select awayteam, goalsagainst, goalsfor from scores) a group by teamorder by score desc, goal_diff desc;

这是表格

 id  | hometeam | awaytem | goalsfor | goalsagainst | time | data 1   | team a   | team b  |   3      |    2         | 13:00| 2016-04-21 2   | team c   | team b  |   4      |    1         | 13:00| 2016-04-19

我不知道如何为所有球队选择最近的 4 场比赛我这样试过:

 (    select hometeam team, goalsfor, goalsagainst from scores   union all    select awayteam, goalsagainst, goalsfor from scores where data>2016-03-21)

但有些球队在那段时间只打了一场比赛，其他球队打了 4 场

Answer 1

您的 FROM 子查询就快完成了，但您需要包含日期。

from (
    select hometeam team, goalsfor, goalsagainst, date from scores 
     union all
    select awayteam team, goalsagainst, goalsfor, date from scores
) a 

现在您需要选择每支球队的最后 4 场比赛，为此您需要使用变量。

SELECT *
FROM (
    SELECT A.*, 
           @rn := IF( @team = team,
                      @rn + 1, 
                      IF( @team := team, 1, 1)
                    ) as rn
    FROM (
        select hometeam team, goalsfor, goalsagainst, date from scores 
         union all
        select awayteam team, goalsagainst, goalsfor, date from scores
    ) a
    CROSS JOIN ( SELECT @rn := 0, @team := '' ) as var
    ORDER BY team, date DESC 
   ) T
WHERE T.rn <= 4   
ORDER BY team, rn  

现在您可以对该结果进行聚合:

SELECT team, count(*), .....
FROM ( /* previous query */ )  as F
GROUP BY team