我知道用 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 | |
------------------------------------------------------------

我该怎么做？

câu trả lời hay nhất

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

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

kết quả:

+-------------+-------+-------+-------+-------+-------+
| 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 |
+-------------+-------+-------+-------+-------+-------+

关于mysql - 我如何选择每支球队的最后五场比赛并确定胜/负/平局？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/34013460/