超过3000列的mySql动态数据透视查询 [英] mySql Dynamic Pivot Query with over 3000 columns
问题描述
早安, 我正在使用动态数据透视查询按月生成产品销售的交叉表.超过3K的产品,因此意味着超过3k的列.当我运行查询时,我得到一个错误.如果我将原始表中的行数限制为1586以下,则可以正常运行,输出为16 col,包括"date"字段.我无法解决这个问题,需要一些帮助!请查看下面的代码和错误:
Good Day, I'm using a dynamic pivot query to generate a cross tab of product sales by month. There are just over 3K products so that means over 3k columns. When I run the query I get an error. If I limit the number of rows in the original table to under 1586 it runs fine with an output of 16 col, including the 'date' field. I can't figure this out and need some help! Please see the code and error below:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(case when PSHDSTK = ''',
PSHDSTK,
''' then MthSales end) AS `',
PSHDSTK,
'`'
)
) INTO @sql
FROM salesbyrow;
SET @sql = CONCAT('SELECT thedate, ', @sql, '
FROM salesbyrow
GROUP BY thedate');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
我得到的错误是
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM salesbyrow
GROUP BY thedate' at line 2
推荐答案
在一个小样本上,一切似乎对我来说都很好.
您确定在PSHDSTK
列中没有引号或某些东西可能破坏您的@sql
字符串吗?
Everything seems to be working just fine for me on a small sample.
Are you sure there are no quotes or something that may break your @sql
string in the PSHDSTK
column?
添加SELECT @sql
用于调试目的,然后执行语句(如下所示).
Add SELECT @sql
for debugging purposes before execution of your statement (shown below).
此外,请注意字符串变量和GROUP_CONCAT的MySQL最大大小.但这在执行查询之前查看时应该很清楚.
Also, beware of MySQL max size for a string variable and GROUP_CONCAT. But that should become clear when you view your query before executing it.
如果GROUP_CONCAT的最大长度为限制(默认为1024),则应更改其长度的临时设置(会话范围).通过以下方式完成:
If GROUP_CONCAT max length is the limit (1024 by default) you should alter the temporary setting (session-scope) for length of it. It's done by:
SET SESSION group_concat_max_len = 10000 -- to set it to 10 000
示例:
Sample:
create table salesbyrow(thedate int, PSHDSTK varchar(2), MthSales int);
insert into salesbyrow(thedate,PSHDSTK,MthSales)
values (1, 'a1', 6),(1, 'a2', 5), (1, 'a1', 3);
您的代码:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(case when PSHDSTK = ''',
PSHDSTK,
''' then MthSales end) AS `',
PSHDSTK,
'`'
)
) INTO @sql
FROM salesbyrow;
SET @sql = CONCAT('SELECT thedate, ', @sql, ' FROM salesbyrow GROUP BY thedate');
健全性检查@sql
变量:
select @sql;
声明看起来像这样(正确):
Statement looks like this (correct):
SELECT thedate, max(case when PSHDSTK = 'a1' then MthSales end) AS `a1`,max(case when PSHDSTK = 'a2' then MthSales end) AS `a2` FROM salesbyrow GROUP BY thedate
现在执行中...
prepare stmt from @sql;
execute stmt;
结果:
thedate a1 a2
1 6 5
这篇关于超过3000列的mySql动态数据透视查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!