mysql - 不使用聚合函数从一张表中减少一行中的多个记录

Question

SELECT * 
        FROM (
            SELECT id, No ,Date, ShiftType, Name, Dsc1, Dsc2 
        FROM xyz        
         ) T1
        PIVOT
        (
            min(ShiftType) for ShiftType IN ([Day],Evening,Night)
        ) AS T2
        ORDER BY No DESC

从上面的查询得到这个结果

id | No  | Date       | Name | Desc1         | Desc2         | Day | Evening | Night
-------------------------------------------------------------
1  | 101 | 2018-01-05 | Nik  | Day Desc1     | Day Desc2     | Day - -
2  | 101 | 2018-01-05 | Nik  | Evening Desc1 | Evening Desc2 | - Evening -
3  | 101 | 2018-01-05 | Nik  | Night Desc1   | Night Desc  2 | - - Night
4  | 102 | 2018-01-19 | Mgh  | Night Desc1   | Night Desc2   | - - Night

需要的输出如下

id | No | Date       | Name | Desc1Day     | Desc2Day     | Desc1Evening         | Desc2Evening         | Desc1Night       | Desc2Night
-------------------------------------------------------------
1 | 101 | 2018-01-05 | Nik  | Day Desc1Day | Day Desc2Day | Evening Desc1Evening | Evening Desc2Evening | Night Desc1Night | Night Desc2Night
2 | 102 | 2018-01-19 | Mgh  | -            | -            | -                    | -                    | Night Desc1Night | Night Desc2Night  

Answer 1

只需使用聚合即可。将多行折叠成没有它们的单行是零有意义的。

SELECT
  MIN(`id`),
  `No`,
  `Date`,
  `Name`,
  MAX(CASE WHEN `ShiftType` = 'Day'     THEN `dsc1` END)   AS Desc1Day,
  MAX(CASE WHEN `ShiftType` = 'Day'     THEN `dsc2` END)   AS Desc2Day,
  MAX(CASE WHEN `ShiftType` = 'Evening' THEN `dsc1` END)   AS Desc1Evening,
  MAX(CASE WHEN `ShiftType` = 'Evening' THEN `dsc2` END)   AS Desc2Evening,
  MAX(CASE WHEN `ShiftType` = 'Night'   THEN `dsc1` END)   AS Desc1Night,
  MAX(CASE WHEN `ShiftType` = 'Night'   THEN `dsc2` END)   AS Desc2Night
FROM
  xyz
GROUP BY
  `No`,
  `Date`,
  `Name`