MySQL数据透视表中的Group By

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

，当我在查询中使用Group By时，表的变化如下:

   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?

Please help, thank you

Answer 1

使用Coalesce函数将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

Answer 2

这比需要的更困难。

没有聚合的GROUP BY不是正确的SQL。

SELECT  month,
        SUM(A),
        SUM(B),
        ...
    FROM tbl
    GROUP BY month;

应该是您所需要的。这个问题似乎不需要COALESCE()。

SUM()(和许多其他聚合)在做算术时简单地忽略任何NULL单元格。

另一点:COUNT('PilotStationDate')是"错误的"。它计算字符串'PilotStationDate'不为空的次数。也许你指的是COUNT(PilotStationDate) ?

那为什么只计算时间而不计算日期呢?简单地AVG时报不行吗?