添加一个子查询到已巨人查询(Adding a sub query to an already gia

这个问题是类型的跟进这个问题我问了一个月前。

这里是我的三个表。

战士

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

我想有两个额外列。一个用于战斗机的纪录和一个他的对手的记录，都基于事件的日期。所以记录直到日期和包括日期，但没有进一步的比。基本上<=当前行的日期。

期望的结果

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

因此，这里的代码来获得当前的结果：

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,
    b.fighter_id AS opponent_id,
    b.fighting_out_of_country AS opponent_country,
    a.method AS method,
    a.method_type AS method_type,
    a.round AS round,
    a.time AS time,
    c.event_id AS event_id,
    c.event_name AS event,
    c.event_date AS date,
    c.event_city AS event_city,
    c.event_state AS event_state,
    c.event_country AS event_country
FROM
    (
        SELECT 
            IF(fighter_b = :fighter_id_0, fighter_b, fighter_a) AS f_a,
            IF(fighter_b = :fighter_id_1, fighter_a, fighter_b) AS f_b,
            winner,
            method,
            method_type,
            round,
            time,
            event
        FROM 
            fights
        WHERE
            :fighter_id_2 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

下面是我用得到的只是一个战士的记录查询，但我不知道如何将它集成到这个主查询来获取两个战斗机和他的对手的记录。当然，我结束了4个值，所以我可以用PHP和HTML / CSS后操纵它们。（胜/损失/平/ no_contests）

SELECT 
SUM(if(winner = :fighter_id_3, 1, 0)) AS wins,
SUM(if(winner <> :fighter_id_4, 1, 0)) AS losses,
SUM(if(method = "Draw", 1, 0)) AS draws,
SUM(if(method = "No Contest", 1, 0)) AS no_contests

FROM fights
WHERE :fighter_id_5 IN (fighter_a, fighter_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,
    SUM(a.f_a IN (d.fighter_a, d.fighter_b) AND d.winner <=> a.f_a) AS fighter_wincount,
    SUM(a.f_a IN (d.fighter_a, d.fighter_b) AND d.winner IS NOT NULL AND d.winner <> a.f_a) AS fighter_losscount,
    SUM(a.f_a IN (d.fighter_a, d.fighter_b) AND d.method = 'Draw') AS fighter_drawcount,
    SUM(a.f_a IN (d.fighter_a, d.fighter_b) AND d.method = 'No Contest') AS fighter_nocontestcount,
    b.name AS opponent,
    SUM(a.f_b IN (d.fighter_a, d.fighter_b) AND d.winner <=> a.f_b) AS opponent_wincount,
    SUM(a.f_b IN (d.fighter_a, d.fighter_b) AND d.winner IS NOT NULL AND d.winner <> a.f_b) AS opponent_losscount,
    SUM(a.f_b IN (d.fighter_a, d.fighter_b) AND d.method = 'Draw') AS opponent_drawcount,
    SUM(a.f_b IN (d.fighter_a, d.fighter_b) AND d.method = 'No Contest') AS opponent_nocontestcount,
    b.fighter_id AS opponent_id,
    b.fighting_out_of_country AS opponent_country,
    a.method AS method,
    a.method_type AS method_type,
    a.round AS round,
    a.time AS time,
    c.event_id AS event_id,
    c.event_name AS event,
    c.event_date AS date,
    c.event_city AS event_city,
    c.event_state AS event_state,
    c.event_country AS event_country
FROM
    (
        SELECT 
            fight_id,
            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,
            method_type,
            round,
            time,
            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
LEFT JOIN
    (
        SELECT 
            a.fighter_a,
            a.fighter_b,
            a.winner,
            a.method,
            b.event_date
        FROM
            fights a
        INNER JOIN
            events b ON a.event = b.event_id
    ) d ON 
        (a.f_a IN (d.fighter_a, d.fighter_b) OR a.f_b IN (d.fighter_a, d.fighter_b)) AND
        d.event_date < c.event_date
GROUP BY
    a.fight_id
ORDER BY
    c.event_date DESC