mysql - 我如何选择每支球队的最后五场比赛并确定胜/负/平局？

Question

我知道用 PHP 可以做到这一点，但是有没有办法只用 MySQL 来做到这一点？

我有这个数据库:

------------------------------------------------------------
| id | hometeam    | awayteam    | homescore  | awayscore  |
------------------------------------------------------------
| 1  | turtles     | muffins     | 7          | 6          |
| 2  | cheetos     | snakes      | 8          | 1          |
| 3  | muffins     | raspberries | 1          | 5          |
| 4  | raspberries | snakes      | 9          | 9          |
| 5  | muffins     | cheetos     | 2          | 9          |
| 6  | turtles     | snakes      | 5          | 9          |
| 7  | snakes      | turtles     | 7          | 2          |
| 8  | muffins     | raspberries | 6          | 5          |
| 9  | turtles     | cheetos     | 6          | 1          |
| 10 | cheetos     | snakes      | 4          | 1          |
| 11 | muffins     | raspberries | 1          | 5          |
| 12 | raspberries | cheetos     | 4          | 1          |
------------------------------------------------------------

我正在尝试为每支球队输出最近五场比赛(按 id desc):

------------------------------------------------------------
| id | team        | game1 | game2 | game3 | game4 | game5 |
------------------------------------------------------------
| 1  | raspberries | win   | win   | loss  | tie   | win   |
| 2  | cheetos     | loss  | win   | loss  | win   | win   |
| 3  | muffins     | loss  | win   | loss  | loss  | loss  |
| 4  | snakes      | loss  | win   | win   | tie   | loss  |
| 5  | turtles     | win   | loss  | loss  | win   |       |
------------------------------------------------------------

我该怎么做？

Answer 1

从纯粹的学术角度来看，这是另一种选择。

1.获取团队列表。

select hometeam team
from   games
union
select awayteam team
from   games

中间结果:

+-------------+
| team        |
+-------------+
| turtles     |
| cheetos     |
| muffins     |
| raspberries |
| snakes      |
+-------------+

2.每支球队最多参加 5 场比赛，从最老的开始。

select team,
       -- Adjust the game numbers when number of games is less
       -- than 5.
       case when count(distinct g5.id) = 1 then min(g5.id)
            when count(distinct g4.id) = 1 then min(g4.id)
            when count(distinct g3.id) = 1 then min(g3.id)
            when count(distinct g2.id) = 1 then min(g2.id)
            when count(distinct g1.id) = 1 then min(g1.id)
       end g1,
       case when count(distinct g4.id) = 2 then min(g4.id)
            when count(distinct g3.id) = 2 then min(g3.id)
            when count(distinct g2.id) = 2 then min(g2.id)
            when count(distinct g1.id) = 2 then min(g1.id)
       end g2,
       case when count(distinct g3.id) = 3 then min(g3.id)
            when count(distinct g2.id) = 3 then min(g2.id)
            when count(distinct g1.id) = 3 then min(g1.id)
       end g3,
       case when count(distinct g2.id) = 4 then min(g2.id)
            when count(distinct g1.id) = 4 then min(g1.id)
       end g4,
       case when count(distinct g1.id) = 5 then min(g1.id)
       end g5
from(      
    select hometeam team
    from   games
    union
    select awayteam team
    from   games
) t
-- Join to the games table N times for N number of games, starting with
-- most recent (g1) to oldest (g5)
left join games g1 on (t.team in (g1.hometeam, g1.awayteam))
left join games g2 on (t.team in (g2.hometeam, g2.awayteam) and g1.id < g2.id)
left join games g3 on (t.team in (g3.hometeam, g3.awayteam) and g2.id < g3.id)
left join games g4 on (t.team in (g4.hometeam, g4.awayteam) and g3.id < g4.id)
left join games g5 on (t.team in (g5.hometeam, g5.awayteam) and g4.id < g5.id)
group by team

中间结果:

+-------------+------+------+------+------+------+
| team        | g1   | g2   | g3   | g4   | g5   |
+-------------+------+------+------+------+------+
| cheetos     |   12 |   10 |    9 |    5 |    2 |
| muffins     |   11 |    8 |    5 |    3 |    1 |
| raspberries |   12 |   11 |    8 |    4 |    3 |
| snakes      |   10 |    7 |    6 |    4 |    2 |
| turtles     |    9 |    7 |    6 |    1 | NULL |
+-------------+------+------+------+------+------+

3.确定每场比赛的结果。

select team,
case when team = g1.hometeam and g1.homescore > g1.awayscore then 'win'
                  when team = g1.hometeam and g1.homescore < g1.awayscore then 'loss'
                  when team = g1.awayteam and g1.homescore > g1.awayscore then 'loss'
                  when team = g1.awayteam and g1.homescore < g1.awayscore then 'win'
                  when g1.id is null then null
                  else 'tie' end
                  game1,
case when team = g2.hometeam and g2.homescore > g2.awayscore then 'win'
                  when team = g2.hometeam and g2.homescore < g2.awayscore then 'loss'
                  when team = g2.awayteam and g2.homescore > g2.awayscore then 'loss'
                  when team = g2.awayteam and g2.homescore < g2.awayscore then 'win'
                  when g2.id is null then null
                  else 'tie' end
                  game2,
case when team = g3.hometeam and g3.homescore > g3.awayscore then 'win'
                  when team = g3.hometeam and g3.homescore < g3.awayscore then 'loss'
                  when team = g3.awayteam and g3.homescore > g3.awayscore then 'loss'
                  when team = g3.awayteam and g3.homescore < g3.awayscore then 'win'
                  when g3.id is null then null
                  else 'tie' end
                  game3,
case when team = g4.hometeam and g4.homescore > g4.awayscore then 'win'
                  when team = g4.hometeam and g4.homescore < g4.awayscore then 'loss'
                  when team = g4.awayteam and g4.homescore > g4.awayscore then 'loss'
                  when team = g4.awayteam and g4.homescore < g4.awayscore then 'win'
                  when g4.id is null then null
                  else 'tie' end
                  game4,
case when team = g5.hometeam and g5.homescore > g5.awayscore then 'win'
                  when team = g5.hometeam and g5.homescore < g5.awayscore then 'loss'
                  when team = g5.awayteam and g5.homescore > g5.awayscore then 'loss'
                  when team = g5.awayteam and g5.homescore < g5.awayscore then 'win'
                  when g5.id is null then null
                  else 'tie' end
                  game5
from(
    select team,
           case when count(distinct g5.id) = 1 then min(g5.id)
                when count(distinct g4.id) = 1 then min(g4.id)
                when count(distinct g3.id) = 1 then min(g3.id)
                when count(distinct g2.id) = 1 then min(g2.id)
                when count(distinct g1.id) = 1 then min(g1.id)
           end g1,
           case when count(distinct g4.id) = 2 then min(g4.id)
                when count(distinct g3.id) = 2 then min(g3.id)
                when count(distinct g2.id) = 2 then min(g2.id)
                when count(distinct g1.id) = 2 then min(g1.id)
           end g2,
           case when count(distinct g3.id) = 3 then min(g3.id)
                when count(distinct g2.id) = 3 then min(g2.id)
                when count(distinct g1.id) = 3 then min(g1.id)
           end g3,
           case when count(distinct g2.id) = 4 then min(g2.id)
                when count(distinct g1.id) = 4 then min(g1.id)
           end g4,
           case when count(distinct g1.id) = 5 then min(g1.id)
           end g5
    from(      
        select hometeam team
        from   games
        union
        select awayteam team
        from   games
    ) t
    left join games g1 on (t.team in (g1.hometeam, g1.awayteam))
    left join games g2 on (t.team in (g2.hometeam, g2.awayteam) and g1.id < g2.id)
    left join games g3 on (t.team in (g3.hometeam, g3.awayteam) and g2.id < g3.id)
    left join games g4 on (t.team in (g4.hometeam, g4.awayteam) and g3.id < g4.id)
    left join games g5 on (t.team in (g5.hometeam, g5.awayteam) and g4.id < g5.id)
    group by team
) r
left join games g1 on (r.g1 = g1.id)
left join games g2 on (r.g2 = g2.id)
left join games g3 on (r.g3 = g3.id)
left join games g4 on (r.g4 = g4.id)
left join games g5 on (r.g5 = g5.id)
order by team

结果:

+-------------+-------+-------+-------+-------+-------+
| team        | game1 | game2 | game3 | game4 | game5 |
+-------------+-------+-------+-------+-------+-------+
| cheetos     | loss  | win   | loss  | win   | win   |
| muffins     | loss  | win   | loss  | loss  | loss  |
| raspberries | win   | win   | loss  | tie   | win   |
| snakes      | loss  | win   | win   | tie   | loss  |
| turtles     | win   | loss  | loss  | win   | NULL  |
+-------------+-------+-------+-------+-------+-------+