MySQLWhere子句與Union都得到錯誤的結果

我將首先說我仍在大量學習 MySQL，而且我絕對處于那個我知道足以危險的階段。

我有一個包含體育聯盟記分資料的資料庫。我們將贏/輸記錄為 1 分或 0 分。有一個晚上涉及雙人游戲（意味著玩家在一個晚上玩兩次，用于兩種不同的格式）。我的資料的結構是這樣的（只是一個示例，我有數百行，采用不同的格式）：

其中 999 用作代碼編號，讓我們知道比賽尚未進行，或者記分表尚未上交我們進行記錄。（我使用 PHP 將這些呼叫到一個網站讓用戶查看發生了什么，并使用 IF 陳述句將網站上的 999 轉換為“TBD”）

我可以分別提取 Format 1 和 Format 2 的分數并得到一個很好的串列，但是當我嘗試將它們拉在一起并獲得總分時，我得到的計數不正確。我知道錯誤在于我的 WHERE 子句，但我一直在努力讓它正常作業，我認為我只需要多加注意。

我當前的 SQL 查詢如下：

SELECT Team, 
       SUM(TotalF1) AS TotalF1, 
       SUM(TotalF2) AS TotalF2, 
       SUM(TotalF1 TotalF2) AS Total
FROM ( ( SELECT HomeTeam AS Team, 
                HF1 AS TotalF1, 
                HF2 AS TotalF2 
         FROM tbl_teamscores 
         WHERE FID = 44 
           AND Type = 'PL' 
           AND HF1 != 999 
           AND HF2 != 999 ) 
       UNION ALL 
       ( SELECT AwayTeam, 
                AF1, 
                AF2 
         FROM tbl_teamscores 
         WHERE FID = 44 
           AND Type = 'PL' 
           AND AF1 != 999 
           AND AF2 != 999 )
      ) CC 
GROUP BY Team 
ORDER BY Total desc, Team ASC;

I am getting incorrect totals though, and I know the reason is because of those 999 designations, as the WHERE clause is skipping over ALL lines where either home or away score matches 999.

I tried separating it out to 4 separate Select Statements, and unioning them, but I just get an error when I do that. I also tried using Inner Join, but MySQL doesn't seem to like that either.

Edit to add DBFiddle with Real World Table Data and queries: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1d4d090b08b8280e734218ba32db6d88 An example of the problem can be observed when looking at the data for Player 10. The overall total should be 13, but I am only getting 12.

Any suggestions would be very helpful.

Thanks in advance!

uj5u.com熱心網友回復：

您可以使用條件聚合：

SELECT Team, 
       SUM(CASE WHEN Total8 <> 999 THEN Total8 END) AS Total8, 
       SUM(CASE WHEN TotalLO <> 999 THEN TotalLO END) AS TotalLO, 
       SUM(CASE WHEN Total8 <> 999 THEN Total8 END)   SUM(CASE WHEN TotalLO <> 999 THEN TotalLO END) AS Total
FROM (
  SELECT HomeTeam AS Team, Home8PTS AS Total8, HomeLOPTS AS TotalLO FROM tbl_teamscores WHERE FID = 44 AND Type = 'PL' 
  UNION ALL 
  SELECT AwayTeam, Away8PTS, AwayLOPTS FROM tbl_teamscores WHERE FID = 44 AND Type = 'PL' 
) CC 
GROUP BY Team 
ORDER BY Team ASC;

要么：

SELECT Team, 
       SUM(NULLIF(Total8, 999)) AS Total8, 
       SUM(NULLIF(TotalLO, 999)) AS TotalLO, 
       SUM(NULLIF(Total8, 999))   SUM(NULLIF(TotalLO, 999)) AS Total
FROM (
  SELECT HomeTeam AS Team, Home8PTS AS Total8, HomeLOPTS AS TotalLO FROM tbl_teamscores WHERE FID = 44 AND Type = 'PL' 
  UNION ALL 
  SELECT AwayTeam, Away8PTS, AwayLOPTS FROM tbl_teamscores WHERE FID = 44 AND Type = 'PL' 
) CC 
GROUP BY Team 
ORDER BY Team ASC;

如果您null在結果中得到 s，那么您還應該使用COALESCE()：

SELECT Team, 
       COALESCE(SUM(NULLIF(Total8, 999)), 0) AS Total8, 
       COALESCE(SUM(NULLIF(TotalLO, 999)), 0) AS TotalLO, 
       COALESCE(SUM(NULLIF(Total8, 999)), 0)   COALESCE(SUM(NULLIF(TotalLO, 999)), 0) AS Total
FROM (
  SELECT HomeTeam AS Team, Home8PTS AS Total8, HomeLOPTS AS TotalLO FROM tbl_teamscores WHERE FID = 44 AND Type = 'PL' 
  UNION ALL 
  SELECT AwayTeam, Away8PTS, AwayLOPTS FROM tbl_teamscores WHERE FID = 44 AND Type = 'PL' 
) CC 
GROUP BY Team 
ORDER BY Team ASC;

請參閱演示。

標籤：mysql group-by sum case union-all

上一篇：DbUpdateConcurrencyException：資料庫操作預計會影響1行，但實際上影響了0行。-等待_context.SaveChangesAsync(true);

下一篇：如何在Mysql中創建復合外鍵