在mysql中的条件组

问题描述：

I had a mysql query where i need to add some condition in Group by statement , if i use single field in Group by it works but i need two field include in the Group by, here is my query any one please help me to find out the issue

SELECT (CASE 
                WHEN CSR.skill_type = 1 THEN 
                    (SELECT skills_value from cv_skills 
                    WHERE skills_id = CSR.skill_id )
                WHEN CSR.skill_type = 2 THEN 
                    (SELECT ostype_name from  cv_os_type 
                    WHERE ostype_id = CSR.skill_id )
                WHEN CSR.skill_type = 3 THEN 
                    (SELECT dbtype_name from cv_db_type 
                    WHERE dbtype_id = CSR.skill_id)
                WHEN CSR.skill_type = 4 THEN 
                    (SELECT title from candidate_competencies 
                    WHERE id = CSR.skill_id)    
                WHEN CSR.skill_type = 0 THEN 
                    IT.type_name
            END) AS skill_name,
            (CASE 
                WHEN IT.type_parent_id > 0 THEN
                    IT.type_parent_id
                WHEN IT.type_parent_id = 0 THEN
                     CIS.interview_type
            END) AS typeId,
            (CASE 
                WHEN CSR.skill_type = 4 THEN 
                    minimum_rating 
            END) AS minimum_rating,
             AVG( CSR.rate ) AS skill_rating,
                                CSR.skill_type,CIS.created, CC.id
FROM `candidate_interview_skill_rate` `CSR`
LEFT JOIN `candidate_interview_process` `CIP` ON CSR.interview_process_id = CIP.id
LEFT JOIN `candidate_interview_schedule` `CIS` ON CIS.id = CIP.interview_schedules_id AND CIS.archive_date IS NULL
LEFT JOIN `candidate_interview` `CI` ON CI.id = CIS.interview_id AND CI.archived_date IS NULL
LEFT JOIN `interview_type` `IT` ON IT.id = CIS.interview_type
LEFT JOIN `candidate_competencies` `CC` ON CC.id = CSR.skill_id
WHERE CI.candidate_user_id = 39
GROUP BY (CASE
         WHEN CSR.skill_type > 0 THEN
           CSR.skill_id, CSR.skill_type
         ELSE CIS.interview_type
     END)
ORDER BY `CSR`.`skill_type`

答

You can concatenate CSR.skill_id, CSR.skill_type to a single column and use it in select statement. The group by needs to be like this

GROUP BY  CASE
    WHEN CSR.skill_type > 0  THEN
            CONCAT(CSR.skill_id, '-', CSR.skill_type)
    ELSE 
            CIS.interview_type
    END;

Hope this works.

答

You can include multiple expressions in a GROUP BY clause, just separate the expressions with commas, just like in the SELECT list.

Very often, the expressions in the SELECT list are repeated in the GROUP BY clause.

It's often possible to use a CASE expression to support a more complex set of conditions.

It's hard to provide more concrete assistance with your query, absent sample data, and desired output.

相关推荐