具有3个表的复杂IF语句 [英] Complex IF statement with 3 tables

问题描述

这是这个问题从昨天开始的延续.

这是我的三个桌子:

战斗机表

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对应的整数.

So in the fights table, fighter_a, fighter_b, and winner are integers that correspond to fighter_id in the Fighters table.

我基本上是在一个基于fighter_id($ fighter_id)检索数据的页面上.

I'm basically on a page retrieving data based on fighter_id ($fighter_id).

我正在尝试与该战斗机的每次战斗创建行，包括其对手的姓名，结果(胜利，失败，平局或nc)，方法，event_name和event_date.挑战在于，获胜者可以是fighter_a或fighter_b.它并不总是在同一列中.感谢您能提供的任何帮助.

I'm trying to create rows with each fight of that fighter that includes his opponent's name, result (win, loss, draw, or nc), method, event_name, and event_date. The challenge is that a winner can be in either fighter_a or fighter_b. It's not always in the same column. I appreciate any help I can get.

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

推荐答案

使用带有条件的子选择将要查找的fighter_id切换到column_b(如果它位于column_b中)，这样可以简化您的操作并加入外部查询:

Use a subselect with conditionals to switch the fighter_id you're looking for to column_a if it's in column_b, that way, it simplifies your operations and joins in the outer query:

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

此外，如果获胜者字段为空，则只需回显方法字段.这样，当您想在系统中添加赢家为null的另一种方法时，不必对CASE语句进行更多的条件检查.

Also, if the winner field is null, then just echo the method field. That way, when you want to add yet another type of method where winner is null to your system, you don't have to keep tacking on more conditional checks to your CASE statement.

这篇关于具有3个表的复杂IF语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！