mysql联查中使用if和group by会让你的结果不是你想要的

mysql中的if语句遇到统计count group by的时候会出现不准确的情况，原因是分组后if条件的结果以第一条为准，不会跟着分组

 

例如：

SELECT t1.*,t2.nick_name,t2.avatar,IF(t1.user_id=104080,1,0) AS is_owner,
IF(t4.user_id=104080,t4.vote_val,'N') AS is_voted,
IF(t7.user_id=104080,1,0) AS is_collected 
FROM answer t1 
JOIN user t2 ON t1.user_id = t2.id 
LEFT JOIN answer_vote t4 ON t4.item_id = t1.id LEFT JOIN answer_collection t7 ON t7.item_id = t1.id HAVING approved = 1 AND question_id = 100134 ORDER BY id desc LIMIT 10 OFFSET 0;

 

 

#加入group by
SELECT t1.*,t2.nick_name,t2.avatar,IF(t1.user_id=104080,1,0) AS is_owner,
IF(t4.user_id=104080,t4.vote_val,'N') AS is_voted,
IF(t7.user_id=104080,1,0) AS is_collected 
FROM answer t1 
JOIN user t2 ON t1.user_id = t2.id 
LEFT JOIN answer_vote t4 ON t4.item_id = t1.id LEFT JOIN answer_collection t7 ON t7.item_id = t1.id GROUP BY id HAVING approved = 1 AND question_id = 100134 ORDER BY id desc LIMIT 10 OFFSET 0;

 

转载于:https://www.cnblogs.com/jiajialu/p/10088054.html