mySQL子查詢計算勝率

我有一個表，我儲存了一個隨機骰子遊戲的數據，如果兩個骰子之間的總和為 7，你就贏了。這是表格：

我需要顯示每個玩家的獲勝百分比，這是現在的查詢，但它不起作用，它為每個玩家返回相同的百分比：

SELECT DISTINCT player_id,
ROUND(((SELECT COUNT(id_game) FROM game WHERE result = 'WIN' ) / 
(SELECT COUNT(id_game) FROM game )) * 100 ) AS percentage
FROM game
group by player_id

你認為有可能做到這一點嗎？我開始懷疑了

SELECT player_id,
      COUNT(result) total,
      ROUND(100 * SUM(result = 'WIN') / COUNT(result)) winning_percent,
      ROUND(100 * SUM(result = 'LOSE') / COUNT(result)) loosing_percent
FROM game
GROUP BY player_id;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a04d576b918c392f90c1d835fd1a6f51

我所做的如下（下面的所有程式碼都可以在這裡找到——另一種解決方案可以在這裡找到）：

解決方案（所問問題）：

CREATE TABLE score
(
 id INTEGER             NOT NULL AUTO_INCREMENT PRIMARY KEY,
 result CHAR(1)         NOT NULL,
 die_1 TINYINT UNSIGNED NOT NULL,
 die_2 TINYINT UNSIGNED NOT NULL,
 player_id INTEGER      NOT NULL,
 
 CONSTRAINT result_w_l_ck  CHECK (result IN ('W', 'L')),
 CONSTRAINT die_1_throw_ck CHECK (die_1 BETWEEN 1 AND 6),
 CONSTRAINT die_2_throw_ck CHECK (die_2 BETWEEN 1 AND 6)
 
);

請注意NOT NULL要求和其他約束 - 它們不僅可以保護數據的完整性（您的數據庫是您防止垃圾輸入的最後一道防線！），它還有助於優化器選擇最佳計劃。請注意，CHECK在 MySQL 版本 >= 8.0.16 之前不會強制執行約束 - 令人難以置信的是，在此之前的版本會“解析”它們但不強制執行它們 - 你一定喜歡 MySQL！

填充表格：

INSERT INTO score VALUES
(1, 'L',    4,  4,  9),
(2, 'L',    4,  5,  7),
(3, 'W',    4,  3,  9),
(4, 'L',    2,  3,  9),
(5, 'W',    6,  1,  7),
(6, 'W',    1,  6,  7),
(7, 'L',    1,  3,  7);

然後我建立（注意建立）以下查詢：

SELECT 
 player_id,
 SUM(CASE 
   WHEN result = 'W' THEN 1
   ELSE 0
 END) AS wins,
 SUM(CASE 
   WHEN result = 'L' THEN 1
   ELSE 0
 END) AS losses,
 COUNT(player_id) AS cnt,
 (SUM(CASE 
   WHEN result = 'W' THEN 1
   ELSE 0
 END))/(COUNT(player_id)) AS w_proportion,
 
 ROUND((SUM(CASE 
   WHEN result = 'W' THEN 1
   ELSE 0
 END))/(COUNT(player_id)) * 100, 2) AS w_percent  
 
FROM score
GROUP BY player_id
ORDER BY player_id;

結果：

player_id   wins    losses  cnt w_proportion    w_percent
       7      2         2    4       0.5000        50.00
       9      1         2    3       0.3333        33.33

因此，當您向下移動查詢（以及跨結果）時，您可以跟踪最終結果是如何得出的。

在這裡要非常小心 - MySQL 執行 INTEGER 劃分與其他系統不同 - SQL 標準要求- 請參閱此處PostgreSQL 1 fiddle5/2 = 2的底部- 另外，請查看如何通過使用強制轉換獲得最終結果（SQL 的 PostgreSQL 簡寫標準。::<data_type>``CAST (variable AS data_type>)

此外，MySQL 使用另一個（！）非標準“技巧”來允許對布爾值求和——正如你從 PostgreSQL 小提琴中看到的那樣，這在其他伺服器上不起作用——再次，要小心！

1 PostgreSQL 幾乎是最符合標準的 RDBMS - 如果您使用其他一些伺服器執行程式碼，您會發現它在這些伺服器上也會失敗。

替代解決方案（小提琴）：

由於結果取決於兩次投擲的得分，因此我們實際上並不需要該列 - 如下所示：

CREATE TABLE score -- Note, no result field!
(
 id INTEGER             NOT NULL AUTO_INCREMENT PRIMARY KEY,
 die_1 TINYINT UNSIGNED NOT NULL,
 die_2 TINYINT UNSIGNED NOT NULL,
 player_id INTEGER      NOT NULL,
 
 CONSTRAINT die_1_throw_ck CHECK (die_1 BETWEEN 1 AND 6),
 CONSTRAINT die_2_throw_ck CHECK (die_2 BETWEEN 1 AND 6),
 CONSTRAINT d1_p_d2_lt_13_ck CHECK ((die_1 + die_2) < 13)
 
);

填充：

INSERT INTO score VALUES
(1, 4,  4,  9),
(2, 4,  5,  7),
(3, 4,  3,  9),
(4, 2,  3,  9),
(5, 6,  1,  7),
(6, 1,  6,  7),
(7, 1,  3,  7);

和 SQL：

SELECT 
 player_id,

 SUM(CASE 
   WHEN die_1 + die_2 = 7 THEN 1
   ELSE 0
 END) AS wins,

 COUNT(player_id) - 
 SUM(CASE 
   WHEN die_1 + die_2 = 7 THEN 1
   ELSE 0
 END) AS losses,
 
 SUM(CASE 
   WHEN die_1 + die_2 = 7 THEN 1
   ELSE 0
 END)/COUNT(player_id) AS prop_w,

 ROUND(SUM(CASE 
   WHEN die_1 + die_2 = 7 THEN 1
   ELSE 0
 END)/COUNT(player_id) * 100, 2) AS percent_w

FROM score
GROUP BY player_id
ORDER BY player_id;

結果（相同）：

player_id   wins    losses    prop_w    percent_w
       7      2         2    0.5000        50.00
       9      1         2    0.3333        33.33

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