SQL COUNT 裡面加入 WHERE 過濾
最近在開發 Laravel 專案需要做些統計，eloquent 不太熟悉且效率很差，因此講求效率的查詢仍使用 Raw SQL 前情提要 需要過濾出所有使用者的答對數量、答題總數 期待輸出： UserID CORRECT TOTAL 1 27 45 2 18 61 嘗試過的方式 (SELECT BH.UserID as UID, COUNT(AH.choice = Q.ans) AS correct FROM ans_histories AH, battle_histories BH, questions Q WHERE BH.BHID = AH.battleID AND AH.questionID = Q.QID AND AH.choice = Q.ans GROUP BY BH.UserID) UNION (SELECT BH.UserID, COUNT(*) FROM ans_histories AH, battle_histories BH, questions Q WHERE BH.BHID = AH.battleID AND AH.questionID = Q.QID AND AH.choice IS NOT NULL GROUP BY BH.UserID) 行不通原因：下面那個會被當作獨立的，不會以相同 UserID 跟上面的敘述結合 UserID CORRECT 1 27 2 18 1 45 2 61 解法 SELECT UserID, COUNT(CASE AH.choice WHEN Q.ans THEN 1 ELSE NULL END) AS correct, COUNT(*) FROM ans_histories AH, battle_histories BH, questions Q WHERE BH.BHID = AH.battleID AND AH.questionID = Q.QID AND AH.choice IS NOT NULL GROUP BY BH.UserID; 其中 COUNT(CASE AH.choice WHEN Q.ans THEN 1 ELSE NULL END) 可以替換成 SUM(CASE AH.choice WHEN Q....