MYSQL 搜尋 2 列中的每個名稱並為每個玩家添加關聯的值

我有一個數據庫“game_results”。

它包含以下內容：

PlayerID (int not null primary key auto increment)
player1 (varchar)
p1destroyed (int)
player2 (varchar)
p2destroyed (int)
winner (varchar)

它不是最有效的數據庫（在幾列中多次重複數據），但它是一種跟踪遊戲結果的簡單方法。

每場比賽結束後，玩家輸入他們的名字，他們殺死對方玩家的船隻的點數，然後誰贏了。

我正在嘗試從兩個玩家那裡獲取數據，並將每個玩家的支持點和反對點相加。

我知道這是糟糕的設計咬我的地方，但我寧願嘗試為我計劃的其他事情解決這個問題。

我希望能夠得到一個結果集，讓每個獨特的玩家都知道他們摧毀的點數和他們被對手摧毀的點數。

我已經想出瞭如何以一種方式做到這一點：

SELECT
player1, SUM(p1destroyed) AS Fmov, SUM(p2destroyed) AS Amov 
FROM game_results
GROUP BY player1;

但是我被困在從 p1 和 p2 中獲取唯一值以進行合併。

我認為工會可能是合適的地方，但我似乎無法讓它發揮作用。

請問有什麼想法嗎？

AUNION會這樣工作：

SELECT player, SUM(Fmov), SUM(Amov) FROM 
 (SELECT player1 AS player, SUM(p1destroyed) AS Fmov, SUM(p2destroyed) AS Amov 
    FROM game_results
    GROUP BY player1
 UNION
 SELECT player2 AS player, SUM(p2destroyed) AS Fmov, SUM(p1destroyed) AS Amov
    FROM game_results
    GROUP BY player2) AS temp
 GROUP BY player

另一種選擇是完全連接，但 MySQL 不支持開箱即用，因此編寫時間可能更長。

Glorfindel 的回答在您問題的參數範圍內很好，但我會談談您還可以如何設計它，以防萬一。

基本上，只有一張桌子和player1和player2並不是很好，因為兩個玩家與遊戲有相同的關係，所以它應該以相同的方式表示。如果跟踪哪個是 #1 哪個是 #2 很重要，那麼最好將其視為玩家在遊戲中的成員資格的一個屬性。

CREATE TABLE `player` (`id` PRIMARY KEY, ...);

CREATE TABLE `game_results` (`id` PRIMARY KEY, ...);

CREATE TABLE `game_player` (
 `game_id`,
 `player_id`,
 `destroyed`,
 PRIMARY KEY (`game_id`, `player_id`),
 FOREIGN KEY (`game_id`) REFERENCES `game_results` (`id`),
 FOREIGN KEY (`player_id`) REFERENCES `player` (`id`),
 ...
);

一些懸而未決的問題仍然存在。

• 結果應該記錄在哪裡？

  • 你可以有一個可以為空的外鍵winnerfrom game_resultsto game_player (id)。
  • 你可以有一個屬性result. game_player如果您在 MySQL 5.7 中，這可能是一個列舉，或者是一個可能結果的小表的外鍵。我更喜歡這個，因為它可以更好地適應平局和廢止。

• 聯桌設計自然允許有兩個以上玩家的遊戲。好不好就看情況了。假設我們想限制玩家的數量，我們會怎麼做？

  • 將另一個列舉或外鍵添加到代表“玩家 1”和“玩家 2”概念的列舉表中。添加一個UNIQUE KEY game_player (game_id, player_number). 這就是我的建議。
  • 添加一個FOREIGN KEY game_player(partner) REFERENCES game_player(id). 這適用於您目前的情況，但不是開放式解決方案。
  • 您不能CHECK在 MySQL 中使用約束。這是我不喜歡 MySQL 的原因之一。這是參考。

無論如何，您嘗試建構的最終查詢看起來像

SELECT gp1.player_id, SUM(gp1.destroyed) AS Fmov, SUM(gp2.destroyed) AS Fmov 
 FROM `game_player` gp1
 [LEFT | INNER] JOIN `game_player` gp2
   ON gp1.game_id = gp2.game_id
     AND gp1.player_id <> gp2.player_id
 [WHERE gp1.result in (`win`, `loose`, `tie`)]
 GROUP BY gp1.player_id;

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