php - MYSQL SUM IF 返回多行

Question

如果 categori.categori_type != 'comment' 返回 comment null 和 SUM poor,fair,good,vgood,vgood,excellent,yes,no column 是否有任何选项否则返回此列 0。我有超过 1 条评论但是它只返回 1 条评论。

 SELECT 
 categori.s_categori_id,categori.categori_name_en,categori.categori_name_ar,
 categori.categori_type,question.survey_id,question.question_en,
 question.question_ar,
 IF(categori.categori_type != 'comment',SUM(result.poor),0) AS poor,
 IF(categori.categori_type != 'comment',SUM(result.fair),0) AS fair,
 IF(categori.categori_type != 'comment',SUM(result.good),0) AS good,
 IF(categori.categori_type != 'comment',SUM(result.vgood),0) AS vgood,
 IF(categori.categori_type != 'comment',SUM(result.excellent),0) AS 
 excellent,
 IF(categori.categori_type != 'comment',SUM(result.yes),0) AS yes,
 IF(categori.categori_type != 'comment',SUM(result.no),0) As no,
 result.comment 
 FROM survey_categori AS categori 
 INNER JOIN survey_questions AS question
 ON categori.s_categori_id = question.s_categori_id 
 INNER JOIN survey_result AS result 
 ON result.s_question_id = question.survey_id 
 WHERE categori.survey_type = 'class'
 GROUP BY question.survey_id

Answer 1

正确使用 when 和 group by 子句

SELECT 
     categori.s_categori_id,categori.categori_name_en,categori.categori_name_ar,
     categori.categori_type,question.survey_id,question.question_en,
     question.question_ar,
    sum(case when categori.categori_type != 'comment' then result.poor else 0 end) as poor,
    sum(case when categori.categori_type != 'comment' then result.fair else 0 end) as fair,
    sum(case when categori.categori_type != 'comment' then result.good else 0 end) as good,
     sum(case when categori.categori_type != 'comment' then result.vgood else 0 end) as vgood,
     sum(case when categori.categori_type != 'comment' then result.excellent else 0 end) as excellent,
     sum(case when categori.categori_type != 'comment' then result.yes else 0 end) as yes,
     sum(case when categori.categori_type != 'comment' then result.no else 0 end) as no,

     case when categori.categori_type = 'comment' then result.comment   end as rcomment
     FROM survey_categori AS categori 
     INNER JOIN survey_questions AS question
     ON categori.s_categori_id = question.s_categori_id 
     INNER JOIN survey_result AS result 
     ON result.s_question_id = question.survey_id 
     WHERE categori.survey_type = 'class'
     GROUP BY categori.s_categori_id,categori.categori_name_en,categori.categori_name_ar,
     categori.categori_type,question.survey_id,question.question_en,
     question.question_ar,rcomment