Mysql

為什麼將左連接作為子查詢後輸出錯誤

  • March 16, 2019

我有兩個完美的查詢，但是當我加入這兩個查詢時，LEFT JOIN輸出變得錯誤。那麼問題是什麼。？

查詢一：

select Batsman.innings_no,
      bowler,
      sum(Balls)                   as B,
      ifnull(sum(Runs_In_Over), 0) as R,
      ifnull(sum(Zero), 0)         as 0s,
      ifnull(sum(Four), 0)         as 4s,
      ifnull(sum(Six), 0)          as 6s
from (SELECT A.innings_no,
            A.bowler,
            count(*)                   as Balls,
            Sum(B.Runs_Scored)         As 'Runs_In_Over',
            case B.runs_scored
              when 0 then count(*) end as Zero,
            case B.runs_scored
              when 4 then count(*) end as Four,
            case B.runs_scored
              when 6 then count(*) end as Six
     FROM `database`.ball_by_ball A
            INNER JOIN `database`.batsman_scored B
                       using (match_id, over_id, ball_id, innings_no)
     where match_id = 981018
       and innings_no = 2
     GROUP BY A.innings_no, A.bowler, B.runs_scored) as Batsman
group by innings_no, bowler order by bowler;

查詢 2：

select innings_no,
      bowler,
      ifnull(sum(Wides), 0)   as WD,
      ifnull(sum(NoBalls), 0) as NB,
      sum(Extra_runs) as Extra
from (SELECT D.innings_no,
            D.bowler,
            case E.extra_type_id
              when 2 then count(*) end as Wides,
            case E.extra_type_id
              when 4 then count(*) end as NoBalls,
            Sum(E.Extra_Runs)          As 'Extra_runs'
     FROM `database`.ball_by_ball D
            INNER JOIN `database`.extra_runs E
                       using (match_id, over_id, ball_id, innings_no)
     WHERE match_id = 981018
       and innings_no = 2
       and E.Extra_Type_Id IN (2, 4)
     GROUP BY D.innings_no, D.bowler, E.extra_type_id) as Extra
group by innings_no, bowler order by bowler;

加盟查詢：

select Batsman.innings_no,
      Batsman.bowler,
      sum(Balls)                   as B,
      ifnull(sum(Runs_In_Over), 0) as R,
      ifnull(sum(Zero), 0)         as 0s,
      ifnull(sum(Four), 0)         as 4s,
      ifnull(sum(Six), 0)          as 6s,
      ifnull(sum(Wides), 0)        as WD,
      ifnull(sum(NoBalls), 0)      as NB,
      ifnull(sum(Extra_runs), 0)              as Extra
from (SELECT A.innings_no,
            A.bowler,
            count(*)                   as Balls,
            Sum(B.Runs_Scored)         As 'Runs_In_Over',
            case B.runs_scored
              when 0 then count(*) end as Zero,
            case B.runs_scored
              when 4 then count(*) end as Four,
            case B.runs_scored
              when 6 then count(*) end as Six
     FROM `database`.ball_by_ball A
            INNER JOIN `database`.batsman_scored B
                       using (match_id, over_id, ball_id, innings_no)
     where match_id = 981018
       and innings_no = 2
     GROUP BY A.innings_no, A.bowler, B.runs_scored) as Batsman
      LEFT JOIN (SELECT D.innings_no,
                        D.bowler,
                        case E.extra_type_id
                          when 2 then count(*) end as Wides,
                        case E.extra_type_id
                          when 4 then count(*) end as NoBalls,
                        Sum(E.Extra_Runs)          As 'Extra_runs'
                 FROM `database`.ball_by_ball D
                        INNER JOIN `database`.extra_runs E
                                   using (match_id, over_id, ball_id, innings_no)
                 WHERE match_id = 981018
                   and innings_no = 2
                   and E.Extra_Type_Id IN (2, 4)
                 GROUP BY D.innings_no, D.bowler, E.extra_type_id) as Extra using (innings_no, bowler)
group by innings_no, bowler order by bowler;

查詢 1 輸出：

在此處輸入圖像描述

查詢 2 輸出：

在此處輸入圖像描述

加入查詢輸出：

在此處輸入圖像描述

如果您看到查詢 1 和 2 的螢幕截圖並與特定的連接查詢進行比較Bowler，那麼很明顯，當我使用左連接作為子查詢時，結果並不能準確地顯示它在單個查詢中顯示的內容。

那麼問題是什麼。？我需要做些什麼來解決這個問題以及為什麼會發生這個問題？

你很接近，這就是我認為你想要的：

select Batsman.innings_no,
      Batsman.bowler,
      sum(Balls)                   as B,
      ifnull(sum(Runs_In_Over), 0) as R,
      ifnull(sum(Zero), 0)         as 0s,
      ifnull(sum(Four), 0)         as 4s,
      ifnull(sum(Six), 0)          as 6s,
      ifnull(sum(Wides), 0)        as WD,
      ifnull(sum(NoBalls), 0)      as NB,
      ifnull(sum(Extra_runs), 0)              as Extra
from (SELECT A.innings_no,
            A.bowler,
            count(*)                   as Balls,
            Sum(B.Runs_Scored)         As 'Runs_In_Over',
            case B.runs_scored
              when 0 then count(*) end as Zero,
            case B.runs_scored
              when 4 then count(*) end as Four,
            case B.runs_scored
              when 6 then count(*) end as Six
     FROM `database`.ball_by_ball A
            INNER JOIN `database`.batsman_scored B
                       using (match_id, over_id, ball_id, innings_no)
     where match_id = 981018
       and innings_no = 2
     GROUP BY A.innings_no, A.bowler) as Batsman
      LEFT JOIN (SELECT D.innings_no,
                        D.bowler,
                        case E.extra_type_id
                          when 2 then count(*) end as Wides,
                        case E.extra_type_id
                          when 4 then count(*) end as NoBalls,
                        Sum(E.Extra_Runs)          As 'Extra_runs'
                 FROM `database`.ball_by_ball D
                        INNER JOIN `database`.extra_runs E
                                   using (match_id, over_id, ball_id, innings_no)
                 WHERE match_id = 981018
                   and innings_no = 2
                   and E.Extra_Type_Id IN (2, 4)
                 GROUP BY D.innings_no, D.bowler) as Extra using (innings_no, bowler)
group by innings_no, bowler order by bowler;

我的查詢和您的加入查詢的不同之處在於，每個子查詢都按我的innings_no查詢分組bowler。

分組是如何工作的

該GROUP BY子句將限制生成的查詢只為GROUP BY.

在您的所有查詢中，您最初按 3 列分組，這意味著您將為 、 和第 3 列的每個組合獲得一個單獨的innings_no行bowler。對於查詢 1 ，此第 3 列是runs_scored，對於查詢 2 ，此列是extra_type_id。這通常不是與您使用GROUP BY的聚合函式（如SUM）一起使用的有效方式。

但是，在Query 1和Query 2中，您將它們包裝在另一個查詢中，將innings_no和bowler作為GROUP BY，因此您將獲得預期的輸出。

但是在Join Query中，您在應用該 outerLEFT JOIN 之前應用了****Query 1和Query 2GROUP BY中的結果。因此，您的連接查詢將與其他兩個查詢的輸出不同的結果連接在一起。這就是為什麼它似乎沒有按預期工作的原因。

我的查詢應該可以解決問題，但如果它沒有提供正確的結果（因為我錯過了一些你可能已經按照你的方式進行分組的正當理由），那麼只需在應用外部sLEFT JOIN 之後使用。GROUP BY這可能會導致更差的性能，這取決於您的數據，這對您來說可能無關緊要。

LEFT JOIN 查詢 2 到查詢 1

以下是如何簡單地對兩個查詢進行 LEFT JOIN，這應該會根據註釋產生您想要的結果。

SELECT * FROM
(
select Batsman.innings_no,
      bowler,
      sum(Balls)                   as B,
      ifnull(sum(Runs_In_Over), 0) as R,
      ifnull(sum(Zero), 0)         as 0s,
      ifnull(sum(Four), 0)         as 4s,
      ifnull(sum(Six), 0)          as 6s
from (SELECT A.innings_no,
            A.bowler,
            count(*)                   as Balls,
            Sum(B.Runs_Scored)         As 'Runs_In_Over',
            case B.runs_scored
              when 0 then count(*) end as Zero,
            case B.runs_scored
              when 4 then count(*) end as Four,
            case B.runs_scored
              when 6 then count(*) end as Six
     FROM `database`.ball_by_ball A
            INNER JOIN `database`.batsman_scored B
                       using (match_id, over_id, ball_id, innings_no)
     where match_id = 981018
       and innings_no = 2
     GROUP BY A.innings_no, A.bowler, B.runs_scored) as Batsman
group by innings_no, bowler
) q1
LEFT JOIN (
select innings_no,
      bowler,
      ifnull(sum(Wides), 0)   as WD,
      ifnull(sum(NoBalls), 0) as NB,
      sum(Extra_runs) as Extra
from (SELECT D.innings_no,
            D.bowler,
            case E.extra_type_id
              when 2 then count(*) end as Wides,
            case E.extra_type_id
              when 4 then count(*) end as NoBalls,
            Sum(E.Extra_Runs)          As 'Extra_runs'
     FROM `database`.ball_by_ball D
            INNER JOIN `database`.extra_runs E
                       using (match_id, over_id, ball_id, innings_no)
     WHERE match_id = 981018
       and innings_no = 2
       and E.Extra_Type_Id IN (2, 4)
     GROUP BY D.innings_no, D.bowler, E.extra_type_id) as Extra
group by innings_no, bowler
) USING (innings_no, bowler)
ORDER BY bowler

我為創建此查詢所做的只是獲取您的Query 1和Query 2，在它們之間插入一個 LEFT JOIN ，並SELECT * FROM在開頭添加一個和一個USING用於連接的子句。我也放在了ORDER BY外面，因為它不會在裡面產生任何影響，不像GROUP BY.

請注意，可能有更簡單的方法可以獲得您想要的結果，但它需要更深入地了解您想要的輸出以及數據的組織方式，因此超出了此問題的範圍。

引用自：https://dba.stackexchange.com/questions/232285

相關問答

Mysql

為什麼最後一個表不使用索引加入？

  • July 12, 2019
檢視問答
Mysql

MySQL查詢：檢測另一個表中已刪除的行並顯示在結果表中

  • August 31, 2022
檢視問答
Mysql

加入查詢返回 0 行 - 不可能在哪裡

  • March 27, 2022
檢視問答
Mysql

MYSQL 根據每個使用者的前 10 條記錄更新數據

  • December 20, 2021
檢視問答
Mysql

使用子查詢改進 JOIN

  • November 12, 2021
檢視問答
Mysql

如何使用 GROUP BY 更快地更新？

  • May 27, 2021
檢視問答