MySQL查询和数据透视表

Question

我的MySQL数据库具有以下列标题：

month, typeOfWork, totalHours

我有兴趣根据typeOfWork和月份显示结果。例如，所有typeOfWork条目将显示在左侧的第一列中，并且每个字段将在每一列的指定月份总计该特定typeOfWork。因此，我的SQL数据透视表语句当前为：

SELECT
IFNULL(typeOfWork, 'Total') AS TypeOfWork,
SUM(IF(month='JAN',totalHours,NULL)) AS JAN,    
SUM(IF(month='FEB',totalHours,NULL)) AS FEB,    
SUM(IF(month='MAR',totalHours,NULL)) AS MAR,    
SUM(IF(month='APR',totalHours,NULL)) AS APR,    
SUM(IF(month='MAY',totalHours,NULL)) AS MAY,    
SUM(IF(month='JUN',totalHours,NULL)) AS JUN,    
SUM(IF(month='JUL',totalHours,NULL)) AS JUL,    
SUM(IF(month='AUG',totalHours,NULL)) AS AUG,
SUM(IF(month='SEP',totalHours,NULL)) AS SEP,    
SUM(IF(month='OCT',totalHours,NULL)) AS OCT,    
SUM(IF(month='NOV',totalHours,NULL)) AS NOV,    
SUM(IF(month='DEC',totalHours,NULL)) AS DEC

FROM $databasetable GROUP BY typeOfWork
WITH ROLLUP

我目前遇到语法错误，指定了最后一行（从’DEC到ROLLUP’）。我感到困惑的原因是，我只是从脚本中的另一个工作枢轴表改编了该表。我认为差异可能出在我的两个需求上（求和totalHours，其中月份等于列名，而typeOfWork等于第一列中的typeOfWork）。我想知道我的IF语句是否需要AND子句吗？

有人能用这个数据透视表指向我正确的方向吗？这将不胜感激。谢谢！

Answer 1

您的查询是正确的。只有一件事-DEC是MySQL的保留字，该字不能直接用作对象标识符；因此，只需在引号后面加上引号-

SELECT
IFNULL(typeOfWork, 'Total') AS TypeOfWork,
SUM(IF(month='JAN',totalHours,NULL)) AS JAN,    
...
SUM(IF(month='DEC',totalHours,NULL)) AS `DEC`

FROM $databasetable GROUP BY typeOfWork