使用下面的MySQL查询,我创建了一个数据透视表,它几乎与我在寻找的内容完全一样。然而,我想用实际描述替换NULL值,例如SubTotal和GrandTotal。这里是我的PHP输出中显示的数据透视表格式(希望格式有点清晰!)。
Name Division 1 Division 2 Division 3 Division 4 Location
Name 1
Name 2
Name 3
NULL Total Total Total Total
Name 4
Name 5
NULL Total etc
NULL Column Grand Total这是我用来生成表格的查询。在研究这个问题之后,看起来CASE功能就是要走的路。但是,当我将两个CASE行添加到下面的查询中时,它似乎不想工作。返回的mysql_error表示“GROUPING函数不存在”。
SELECT
CASE WHEN (GROUPING(name)=1) THEN 'MainTotal' ELSE name END AS name,
CASE WHEN (GROUPING(location)=1) THEN 'SubTotal' ELSE location END AS location,
name AS Name,
SUM(IF(division='OEM',totalHours,NULL)) AS OEM,
SUM(IF(division='A/M',totalHours,NULL)) AS AM,
SUM(IF(division='SKF',totalHours,NULL)) AS SKF,
SUM(IF(division='RE',totalHours,NULL)) AS RE,
location as Location
FROM $databasetable GROUP BY location, name
WITH ROLLUP有人可以告诉我我做错了什么吗? CASE函数是否替代NULL类别标题的方式?
提前致谢!