使用CASE与GROUP BY时无法将同一enrolment的所有值显示在同一行

问题原因 #

你当前查询同个enrolment返回多行的核心原因是GROUP BY子句包含了come、cena、tipo_ausencia三个字段：你的逻辑中三个字段分别对应permit_type_id为4、5、6/7的许可数据，每条许可记录只会有一个字段非空、另外两个为NULL，按5个字段全部分组后自然会拆成3行单独的记录。

修改方案 #

仅按日期time和登记IDenrolment两个唯一维度分组，对三个业务字段用聚合函数提取非空值即可，修改后的完整查询如下：

SELECT
    time,
    enrolment,
    MAX(come) AS come,
    MAX(cena) AS cena,
    MAX(tipo_ausencia) AS tipo_ausencia
FROM (
        SELECT
            DATE_FORMAT(created_at, '%d/%m/%Y') AS time,
            enrolment_id as enrolment,
            (CASE 
                WHEN permits.permit_type_id= '4' AND permits.status_id = '2' AND permits.permit_type_option_id='3' AND permits.cancel <1 THEN 'No almuerza'
                WHEN permits.permit_type_id= '4' AND permits.status_id = '2' AND permits.permit_type_option_id='4' AND permits.cancel <1 THEN 'Come picnic'
                WHEN permits.permit_type_id= '4' AND permits.status_id = '2' AND permits.permit_type_option_id='1' AND permits.cancel <1 THEN 'Almuerza antes'
                WHEN permits.permit_type_id= '4' AND permits.status_id = '2' AND permits.permit_type_option_id='2' AND permits.cancel <1 THEN 'Almuerza después'
                WHEN permits.permit_type_id= '4' AND permits.status_id = '2' AND permits.permit_type_option_id='5' AND permits.cancel <1 THEN 'Almuerzo con nota'
                ELSE null END  
              ) as come,
              (CASE 
                WHEN permits.permit_type_id= '5' AND permits.status_id = '2' AND permits.permit_type_option_id='8'  THEN 'No cena'
                WHEN permits.permit_type_id= '5' AND permits.status_id = '2' AND permits.permit_type_option_id='9'  THEN 'Cena picnic'
                WHEN permits.permit_type_id= '5' AND permits.status_id = '2' AND permits.permit_type_option_id='6'  THEN 'Cena antes'
                WHEN permits.permit_type_id= '5' AND permits.status_id = '2' AND permits.permit_type_option_id='7'  THEN 'Cena después'
                WHEN permits.permit_type_id= '5' AND permits.status_id = '2' AND permits.permit_type_option_id='10' THEN 'Cena con nota'
               ELSE null END  
              )as cena,
            (CASE 
            WHEN permits.permit_type_id= '6' AND permits.status_id = '2' AND permits.cancel <1 AND DATE(NOW()) between date_start and (date_end-1) THEN 'Duerme fuera'
            WHEN permits.permit_type_id= '7' AND permits.status_id = '2' AND permits.cancel <1 AND (date_start = curdate() OR date_end > curdate()) THEN 'Regresa a dormir'
            ELSE null END  
          ) as tipo_ausencia
        FROM        
            permits
   WHERE (DATE(NOW()) between date_start and (date_end-1) OR (date_start = curdate() OR date_end > curdate())) AND permits.cancel <1
    ) p
GROUP BY 1,2
ORDER BY 2 DESC

补充说明 #

如果存在同个enrolment同一天有多个同类型许可的场景，可以将MAX()替换为GROUP_CONCAT(DISTINCT 字段名 SEPARATOR ',')，拼接多个非空值避免数据丢失。

内容的提问来源于stack exchange，提问作者Marce Castro