无法获得CASE和GROUP BY以显示同一行中的所有值

Question

我很难找到解决这个问题的办法。这是我的疑问：

SELECT
    time,
    enrolment,
    come,
    cena,
    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,3,4,5
 order by 2 desc

其结果是：

​

​

我得到3行相同的“注册”字段。我试着弄到这样的东西：

时间:注册情况:来吧，cena，tipo_ausencia 19/09/2021年，101，无almuerza，无cena，duerme

有什么想法吗？谢谢!

Answer 1

使用MAX()将多个行转换为每个注册的一行，其中一个列有值，另两个列为NULL。

SELECT time
     , enrolment
     , MAX(come) come
     , MAX(cena) cena
     , MAX(tipo_ausencia) tipo_ausencia
FROM (
        Your main query
     ) p
GROUP BY time
       , enrolment
ORDER BY enrolment desc

另一种方式，不需要使用子查询，只有主查询才能达到目的。

SELECT DATE_FORMAT(created_at, '%d/%m/%Y') AS time
     , enrolment_id as enrolment
     , MAX(case logic for come place here) as come
     , MAX(case logic for cena place here) as cena
     , MAX(case logic for tipo_ausencia place here) 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
GROUP BY DATE_FORMAT(created_at, '%d/%m/%Y')
       , enrolment_id
ORDER BY enrolment_id DESC

更易读的案例陈述来。如果permit_type_id、status_id和permit_type_option_id的数据类型是整数，则不使用quation标记。

CASE 
     WHEN permit_type_id = '4' AND status_id = '2' AND cancel < 1
         THEN CASE permit_type_option_id
                  WHEN '3' THEN 'No almuerza'
                  WHEN '4' THEN 'Come picnic'
                  WHEN '1' THEN 'Almuerza antes'
                  WHEN '2' THEN 'Almuerza después'
                  WHEN '5' THEN 'Almuerzo con nota'
              END
     ELSE NULL
END