mysql - 具有3个表的复杂IF语句

这是this question from yesterday的延续。
这是我的三张桌子：
战斗机工作台

fighter_id |  name
-----------------------
1          | John
2          | Steve
3          | Bill
4          | Bobby

事件表

event_id  |  event_name  |  event_date
-------------------------------------------
1          | MMA         |  01/01/2010
2          | Cool        |  02/20/2010
3          | Yeaa!       |  04/15/2010

搏击台

fight_id  |  fighter_a  |  fighter_b  |  winner  |  method  |  event
-----------------------------------------------------------------------
1         | 1           |  2          |  1       | Sub      | 1
2         | 4           |  1          |  4       | KO       | 2
3         | 1           |  3          |  NULL    | Draw     | 3

试图获得的结果

result  |  opponent  |  method  |  event  |  date
----------------------------------------------------------
Draw    | Bill       |  Draw    |  Yeaa!  |  04/15/2010
Loss    | Bobby      |  KO      |  Cool   |  02/20/2010
Win     | Steve      |  Sub     |  MMA    |  01/01/2010

所以在fights表中，fighter_a、fighter_b和winner是与Fighters表中的fighter_id相对应的整数。
我基本上是在一个页面上检索基于fighter_id（$fighter_id）的数据。
我试着在每一场战斗中创建包含对手名字、结果（赢、输、平局或nc）、方法、事件名称和事件日期的行。挑战在于，胜利者可以出现在fighter_a或fighter_ab中，但并不总是在同一列。我很感谢你能帮我。

select
    fight_id,
    CASE
      WHEN winner is not null and winner=fighter_id then 'win'
      WHEN winner is not null and winner<>fighter_id then 'loss'
      WHEN winner is null and method='Draw' then 'draw'
      WHEN winner is null and method = 'No Contest' then 'no contest'
      ELSE ''
    END as match_result,
    participant.name 'participant',
    opponent.name 'opponent'
FROM fights
    INNER JOIN fighters as participant on participant.fighter_id = fights.fighter_a
    INNER JOIN fighters as oppoent on opponent.fighter_id = fights.fighter_b
WHERE
    fighter_a=$fighter_id OR fighter_b=$fighter_id
ORDER BY
    event_date DESC

最佳答案

使用带条件的子选择将要查找的战斗机id切换到列a（如果它在列b中），这样，它可以简化操作并加入外部查询：

SELECT
    (
        CASE
            WHEN a.winner = a.f_a THEN 'Win'
            WHEN a.winner = a.f_b THEN 'Loss'
            WHEN a.winner IS NULL THEN a.method
        END
    ) AS result,
    b.name AS opponent,
    a.method AS method,
    c.event_name AS event,
    c.event_date AS date
FROM
    (
        SELECT
            IF(fighter_b = $fighter_id, fighter_b, fighter_a) AS f_a,
            IF(fighter_b = $fighter_id, fighter_a, fighter_b) AS f_b,
            winner,
            method,
            event
        FROM
            fights
        WHERE
            $fighter_id IN (fighter_a, fighter_b)
    ) a
INNER JOIN
    fighters b ON a.f_b = b.fighter_id
INNER JOIN
    events c ON a.event = c.event_id
ORDER BY
    c.event_date DESC

另外，如果winner字段为空，那么只需回显method字段。这样，当您想在系统中添加另一种类型的winner为空的方法时，就不必继续对CASE语句进行更多的条件检查。