mysql - MySQL 数据透视表中的分组依据

Question

我正在创建一个像这样的数据透视表:

    MONTH   A       B       C       D       E 
     01     NULL    NULL    11.600  NULL    NULL
     01     11.600  NULL    NULL    NULL    NULL
     02     NULL    NULL    6.000   NULL    NULL
     03     NULL    NULL    14.667  NULL    NULL

当我在查询中使用分组依据时，表会发生如下变化:

   MONTH    A      B       C        D       E
   01      NULL    NULL    11.600   NULL    NULL
   02      NULL    NULL    6.000    NULL    NULL
   03      NULL    NULL    14.667   NULL    NULL

这是我正在寻找的表格:

   MONTH    A      B       C        D       E
   01      11.600  NULL    11.600   NULL    NULL
   02      NULL    NULL    6.000    NULL    NULL
   03      NULL    NULL    14.667   NULL    NULL

这是我的查询:

    SELECT 
DISTINCT MONTH , 
CASE WHEN Cabang.ID_Cabang = "C001" THEN PWT END AS A,
CASE WHEN Cabang.ID_Cabang = "C002" THEN PWT END AS B,
CASE WHEN Cabang.ID_Cabang = "C003" THEN PWT END AS C,
CASE WHEN Cabang.ID_Cabang = "C004" THEN PWT END AS D,
CASE WHEN Cabang.ID_Cabang = "C005" THEN PWT END AS E

FROM `keyperformanceindicator`

INNER JOIN pilot ON keyperformanceindicator.ID_Pilot = pilot.ID_Pilot
INNER JOIN cabang ON keyperformanceindicator.ID_Cabang = cabang.ID_Cabang
INNER JOIN 
(

  SELECT 
   DATE_FORMAT( PilotStationDate, '%m' ) AS MONTH , 
   FORMAT( SUM(`PilotWaitingTime`) / COUNT('PilotStationDate'), 3 ) AS PWT, 
   COUNT( 'PilotStationDate' ) AS jumlah

  FROM keyperformanceindicator
  INNER JOIN pilot ON keyperformanceindicator.ID_Pilot = pilot.ID_Pilot
  GROUP BY DATE_FORMAT( PilotStationDate, '%m' )

)x ON DATE_FORMAT( PilotStationDate, '%m' ) = x.Month

GROUP BY DATE_FORMAT(PilotStationDate, '%m')
ORDER BY  UNIX_TIMESTAMP( CONCAT_WS( '', PilotStationDate, ' ', PilotStationTime ) ) ASC

最后。如何将 NULL 更改为 0？

请帮忙，谢谢

Answer 1

使用 Coalesce 函数与 case 将 null 值更改为 0。

Coalesce ((CASE WHEN Cabang.ID_Cabang = "C001" THEN PWT END),0) AS A,
Coalesce ((CASE WHEN Cabang.ID_Cabang = "C002" THEN PWT END),0) AS B,
Coalesce ((CASE WHEN Cabang.ID_Cabang = "C003" THEN PWT END),0) AS C,
Coalesce ((CASE WHEN Cabang.ID_Cabang = "C004" THEN PWT END),0) AS D,
Coalesce ((CASE WHEN Cabang.ID_Cabang = "C005" THEN PWT END),0) AS E

更新的查询:

    SELECT 
     MONTH , 
    coalesce((max(CASE WHEN Cabang.ID_Cabang = "C001" THEN PWT END)),0) AS A,
    coalesce((max(CASE WHEN Cabang.ID_Cabang = "C002" THEN PWT END)),0) AS B,
    coalesce((max(CASE WHEN Cabang.ID_Cabang = "C003" THEN PWT END)),0) AS C,
    coalesce((max(CASE WHEN Cabang.ID_Cabang = "C004" THEN PWT END)),0) AS D,
    coalesce((max(CASE WHEN Cabang.ID_Cabang = "C005" THEN PWT END)),0) AS E

    FROM `keyperformanceindicator`

    INNER JOIN pilot ON keyperformanceindicator.ID_Pilot = pilot.ID_Pilot
    INNER JOIN cabang ON keyperformanceindicator.ID_Cabang = cabang.ID_Cabang
    INNER JOIN 
    (

      SELECT 
       DATE_FORMAT( PilotStationDate, '%m' ) AS MONTH , 
       FORMAT( SUM(`PilotWaitingTime`) / COUNT('PilotStationDate'), 3 ) AS PWT, 
       COUNT( 'PilotStationDate' ) AS jumlah

      FROM keyperformanceindicator
      INNER JOIN pilot ON keyperformanceindicator.ID_Pilot = pilot.ID_Pilot
      GROUP BY DATE_FORMAT( PilotStationDate, '%m' )

    )x ON DATE_FORMAT( PilotStationDate, '%m' ) = x.Month

    GROUP BY DATE_FORMAT(PilotStationDate, '%m')
    ORDER BY  UNIX_TIMESTAMP( CONCAT_WS( '', PilotStationDate, ' ', PilotStationTime ) ) ASC