SQL COUNT 裡面加入 WHERE 過濾

Posted by JackKuo on Monday, June 11, 2018

最近在開發 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.ans THEN 1 ELSE 0 END)

參考資料

https://stackoverflow.com/questions/1400078/is-it-possible-to-specify-condition-in-count


comments powered by Disqus