使用下面的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类别标题?提前感谢!
试一下:
SELECT
IFNULL(name, 'MainTotal') AS name,
IFNULL(location, 'SubTotal') AS location,
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
我知道这个问题在这一点上是相当老的,但结果仍然是第一个返回当我搜索谷歌'mysql with rollup labels'
您的响应使用grouping()
函数,这不是MySQL的一部分,但有一个类似的方法来解决您的问题。
我假设您试图避免在执行后调整查询的输出。
尝试以下操作:
SELECT
CASE
WHEN [column1 grouped by] IS NULL THEN "Total"
ELSE [column1]
END AS `[alias]`,
CASE
WHEN [column1 grouped by] IS NULL THEN "---"
ELSE [column2]
END AS `[alias2]`,
SUM(IF([condition1] = [variable], [sum column], 0)) as `[alias3]`
...
FROM [table]
GROUP BY [column1] WITH ROLLUP
这里的逻辑替换了按列分组返回的空值,这在使用ROLLUP
时出现在最后一行。未通过汇总聚合的任何其他列默认为查询返回的最后一行,不包括汇总总数。为了避免这种情况,您可以使用case来检查按列分组的列是否为空,并通过case返回将所选列的值替换为您想要的值。
如果你需要做小计,使用相同的逻辑,但设置查询的单独实例和UNION
它们一起形成你的数据透视表