MySQL 查询和数据透视表

2022-01-22 00:00:00 pivot sql mysql

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

My MySQL database has the following column headings:

month, typeOfWork, totalHours

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

I am interested in displaying the results according to typeOfWork and month. For example, all typeOfWork entries will be displayed in the first column on the left, and each field will total that particular typeOfWork in each column's specified month. As such, my SQL pivot table statement is currently:

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")时遇到语法错误.我的困惑来自于我只是从脚本中的另一个工作数据透视表中改编了这个表.我认为差异可能在于我的两个要求(总和，其中月份等于列名和 typeOfWork 等于第一列中的 typeOfWork).我想知道我的 IF 语句是否需要 AND 子句?

I'm currently getting a syntax error specifying the last line (from 'DEC through to ROLLUP'). My confusion comes from the fact that I have simply adapted this table from another working pivot table in my script. I think the difference might be in my two requirements (sum totalHours where month equals column name AND typeOfWork equals typeOfWork in first column). I'm wondering if my IF statements require an AND clause?

谁能用这个数据透视表为我指明正确的方向?这将不胜感激.谢谢！

Can anybody point me in the right direction with this pivot table? It would be greatly appreciated. Thank you!

推荐答案

您的查询是正确的.只有一件事——DEC 是一个 MySQL 保留字，这个词不能直接作为对象标识符；所以，只需用反引号引用它 -

Your query is correct. Only one thing - DEC is a MySQL reserved word, this word cannot be used as object identifier directly; so, just quote it with backticks - 

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

相关文章