mysql decs倒叙查询,MySQL查询和数据透视表

My MySQL database has the following column headings:

month, typeOfWork, totalHours

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 columns 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

Im 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). Im 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!

解决方案

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

My MySQL database has the following column headings: month, typeOfWork, totalHours 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 columns 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 Im 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). Im 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! 解决方案 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