向已经很大的查询添加子查询 [英] Adding a sub query to an already giant query

问题描述

这个问题是我问的这个问题的后续一个月前.

This question is kind of a follow up to this question I asked a month ago.

这是我的三张桌子.

战士

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

所以这些是当前的结果(还有很多其他列，但它们与问题无关)

So these are the current results (there's a bunch of other columns but they're not relevant to the question)

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

我想要两个额外的列.一项用于战斗机的记录，另一项用于他的对手的记录，均基于事件的日期.所以记录到那个日期，包括那个日期，但不能超过那个日期.基本上 <= 到当前行的日期.

I want to have two extra columns. One for fighter's record and one for his opponents record, both based on the date of the event. So record until that date and including that date but not further than that. Basically <= to the current row's date.

预期结果

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

这里是获取当前结果的代码:

So here's the code to get the current results:

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)

Here's the query I'm using to get just one fighter's record, but I'm not sure how to integrate it into this main query to fetch records of both fighter and his opponent. Of course I end up with 4 values so I can manipulate them with PHP and HTML/CSS later. (wins/losses/draws/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

这篇关于向已经很大的查询添加子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！