最近在開發 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