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