在此查詢中，我在下面提供的影像中得到以下結果。但是，我想更進一步，將計數轉換為獲勝次數/總獲勝次數的比率。
但是，當我使用第二個查詢（在底部）時，我收到一個錯誤，無效使用 group 函式。
為了解釋一下，計數基于查詢中每個唯一列組合的分組
有人可以解釋一下我做錯了什么嗎？
SELECT summoner_id, monster_1_id, monster_2_id, monster_3_id,
monster_4_id, monster_5_id, monster_6_id, Count(*)/
( SELECT SUM(Count(*)) ) AS Ratio
FROM battledata.history
GROUP BY summoner_id, monster_1_id, monster_2_id, monster_3_id,
monster_4_id, monster_5_id, monster_6_id ;
SELECT summoner_id, monster_1_id, monster_2_id, monster_3_id,
monster_4_id, monster_5_id, monster_6_id, Count(*)/
( SELECT SUM(Count(*)) ) AS Ratio
FROM battledata.history
GROUP BY summoner_id, monster_1_id, monster_2_id, monster_3_id,
monster_4_id, monster_5_id, monster_6_id ;
uj5u.com熱心網友回復：
您需要COUNT(*)用作子查詢（如果有 where 子句，則它需要在兩個查詢中匹配）：
SELECT
summoner_id,
monster_1_id,
monster_2_id,
monster_3_id,
monster_4_id,
monster_5_id,
monster_6_id,
COUNT(*) / (SELECT COUNT(*) FROM battledata.history) AS Ratio
FROM battledata.history
GROUP BY
summoner_id,
monster_1_id,
monster_2_id,
monster_3_id,
monster_4_id,
monster_5_id,
monster_6_id
編輯：
如果使用 MySQL 8 或更高版本，您可以使用視窗函式。更正語法后，您的原始查詢將起作用：
SELECT
summoner_id,
monster_1_id,
monster_2_id,
monster_3_id,
monster_4_id,
monster_5_id,
monster_6_id,
COUNT(*) / SUM(COUNT(*)) OVER () AS Ratio
FROM battledata.history
GROUP BY
summoner_id,
monster_1_id,
monster_2_id,
monster_3_id,
monster_4_id,
monster_5_id,
monster_6_id
uj5u.com熱心網友回復：
SELECT summoner_id, monster_1_id, monster_2_id, monster_3_id, monster_4_id, monster_5_id, monster_6_id
,COUNT(*) / (SELECT COUNT(*) FROM battledata.history) AS Ratio
FROM battledata.history
GROUP BY summoner_id ,monster_1_id,monster_2_id,monster_3_id
,monster_4_id,monster_5_id,monster_6_id
這應該可以滿足您的需求。如果您需要添加 WHERE 子句，請記住在兩個查詢中添加相同的內容
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/353211.html