MySQL查询和数据透视表 [英] MySQL Query and Pivot Tables

问题描述

我的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').我感到困惑的原因是，我只是从脚本中的另一个工作枢轴表改编了该表.我认为差异可能出在我的两个需求上(求和totalHours，其中月份等于列名，而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

这篇关于MySQL查询和数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！