count, sum和 if,case when

sum有时也可以理解为count 

count不一定会得到正确的答案

SELECT

AcUserID,
r.RoundID,
sum(if(r.MyGuess=e.ResultGuess, 1,0)) as rightno

FROM
tbStockActiveUserGuessRec201609 r
LEFT JOIN tbStockActiveGuessResult e ON r.MarketDay = e.MarketDay

GROUP BY
r.AcUserID,

r.RoundID

****************************************

SELECT
AcUserID,
r.RoundID,
count(if(r.MyGuess=e.ResultGuess, 1,0)) as rightno

FROM
tbStockActiveUserGuessRec201609 r
LEFT JOIN tbStockActiveGuessResult e ON r.MarketDay = e.MarketDay

GROUP BY
r.AcUserID,

r.RoundID

****************************************

SELECT
AcUserID,
r.RoundID,
if(r.MyGuess=e.ResultGuess, 1,0)) as FType
count(1) as RightErrNo
FROM
tbStockActiveUserGuessRec201609 r
LEFT JOIN tbStockActiveGuessResult e ON r.MarketDay = e.MarketDay

GROUP BY
r.AcUserID,

r.RoundID,

FType

IFNULL(SUM(CASE WHEN a.MyGuess=b.ResultGuess THEN 1  END),0) as rightno