具有基于单列数据的动态头的MySQL数据透视表 [英] MySQL pivot table with dynamic headers based on single column data
本文介绍了具有基于单列数据的动态头的MySQL数据透视表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
SELECT cs.`category_id `，cs.`ProcessDate`，cs.`PercentChange`
FROM`Category_Statistics` cs
WHERE cs.`ProcessDate`> ='2011-05-10'
AND cs.` ProcessDate`< ='2011-05-14'
哪些会返回如下：
CategoryId | ProcessDate | PercentChange
-------------------------------------------
category_4 | 2011-05-10 | 10
category_4 | 2011-05-11 | 18
category_4 | 2011-05012 | 12
...
category_7 | 2011-05-10 | 21
category_7 | 2011-05-11 | 7
...
category_12 | 2011-05-10 | 7
category_12 | 2011-05-11 | 15
现在我想要的结果是这样的（从MySQL查询，不由app）：
CategoryId | 2011-05-10 | 2011-05-11 | 2011-05-12 | 2011-05-13 | 2011-05-14 |
---------------------------------------------- ----------------------------------
category_4 | 10 | 18 | 12 | 9 | 14 |
category_7 | 21 | 7 | 16 | 14 | 13 |
categeory_12 | 7 | 15 | 11 | 19 | 8 |
---------------------------------------------- ----------------------------------
有两个注意事项：
-
日期范围可以增长或收缩
（取决于查询） -
在一些
的情况下，PercentChange可能为空（让我们说category_7 /
2011-05-12可能没有设置值）
所以最终我不太确定如何构建选择部分查询以反映动态列数（我知道它与CONCAT有关）。
编辑 - >部分工作代码 - >
SELECT`CategoryId`，
MAX（IF（c.`ProcessedOn` ='2011-04-20' c.`PercentChange`，NULL））AS'2011-04-20'，
MAX（IF（c.`ProcessedOn` ='2011-04-21'，c.`PercentChange`，NULL））AS '2011-04-21'，
MAX（IF（c.`ProcessedOn` ='2011-04-22'，c.`PercentChange`，NULL））AS'2011-04-22'，
MAX（IF（c.`ProcessedOn` = '2011-04-23'，c.`PercentChange`，NULL））AS'2011-04-23'，
MAX（IF（c.`ProcessedOn` ='2011-04-24'，c。 `PercentChange`，NULL））AS'2011-04-24'
FROM`Category_Gravity` c
WHERE c.`ProcessedOn`> ='2011-04-20'
AND c .`ProcessedOn`< ='2011-04-24'
GROUP BY`CategoryId`
我现在需要做的是将
MAX（IF（c.`ProcessedOn` ='2011-04- 20'，c.`PercentChange`，NULL））AS'2011-04-20'，
进入更动态的东西（因为日期范围会改变）
解决方案
看看这个类似的线程，我写了一个sp完成任务
编辑。更新答案
创建表'pivot`（
`id` int（11）not null auto_increment ，
`categoryid` int（11）default null，
`processdate`日期默认null，
`percentchange` int（11）默认null，
主键（`id` ）
）engine = myisam auto_increment = 9 default charset = latin1;
/ *数据表'pivot` * /
插入`pivot`（`id`，`categoryid`，`processdate`，`percentchange`）值（1,4， '2011-05-10'，1）;
插入`pivot`（`id`，`categoryid`，`processdate`，`percentchange`）值（2,4，'2011-05-11'，22）;
插入`pivot`（`id`，`categoryid`，`processdate`，`percentchange`）值（3,4，'2011-05-12'，3）;
插入`pivot`（`id`，`categoryid`，`processdate`，`percentchange`）值（4,7，'2011-05-10'，4）;
插入`pivot`（`id`，`categoryid`，`processdate`，`percentchange`）值（5,7，'2011-05-11'，5）;
插入`pivot`（`id`，`categoryid`，`processdate`，`percentchange`）值（6,12，'2011-05-10'，6）;
插入`pivot`（`id`，`categoryid`，`processdate`，`percentchange`）值（7,12，'2011-05-12'，7）;
插入`pivot`（`id`，`categoryid`，`processdate`，`percentchange`）值（8,4，'2011-05-13'，12）;
分隔符//
删除过程如果存在dynamic_view2 //
创建过程dynamic_view2（在日期，在edate日期）
begin
declare finish int default 0;
声明cdate date;
declare str varchar（10000）defaultselect categoryid;
声明curs对于select processdate的光标，其中在sdate和edate group之间的processdate中由processdate;
声明继续处理程序未找到set finish = 1;
打开curs;
my_loop：loop
fetch curs into cdate;
如果finish = 1然后
离开my_loop;
end if;
set str = concat（str，max（case when processdate ='，cdate，'then percentchange else null end）as`，cdate，`，）;
end loop;
关闭curs;
set str = substr（str，1，char_length（str）-1）;
set @str = concat（str，from pivot
group by categoryid）;
从@str准备stmt;
执行stmt;
deallocate prepare stmt;
end; //
delimiter;
mysql>调用dynamic_view2（'2011-05-10'，'2011-05-13'）;
+ ------------ + ------------ + ------------ + ------ ------ + ------------ +
| categoryid | 2011-05-10 | 2011-05-11 | 2011-05-12 | 2011-05-13 |
+ ------------ + ------------ + ------------ + ------ ------ + ------------ +
| 4 | 1 | 22 | 3 | 12 |
| 7 | 4 | 5 | NULL | NULL |
| 12 | 6 | NULL | 7 | NULL |
+ ------------ + ------------ + ------------ + ------ ------ + ------------ +
3行集（0.00秒）
I am trying to write a query to create a 'table' of data as follows:
SELECT cs.`category_id`, cs.`ProcessDate`, cs.`PercentChange`
FROM `Category_Statistics` cs
WHERE cs.`ProcessDate` >= '2011-05-10'
AND cs.`ProcessDate` <= '2011-05-14'
Which would return something like:
CategoryId | ProcessDate | PercentChange
-------------------------------------------
category_4 | 2011-05-10 | 10
category_4 | 2011-05-11 | 18
category_4 | 2011-05012 | 12
...
category_7 | 2011-05-10 | 21
category_7 | 2011-05-11 | 7
...
category_12 | 2011-05-10 | 7
category_12 | 2011-05-11 | 15
Now I want the results to be something like this (from a MySQL query, not manipulated by the app):
CategoryId | 2011-05-10 | 2011-05-11 | 2011-05-12 | 2011-05-13 | 2011-05-14 |
--------------------------------------------------------------------------------
category_4 | 10 | 18 | 12 | 9 | 14 |
category_7 | 21 | 7 | 16 | 14 | 13 |
categeory_12 | 7 | 15 | 11 | 19 | 8 |
--------------------------------------------------------------------------------
There are two caveats to this:
The date range can grow or shrink (depending on the query)
PercentChange may be null in some cases (lets say category_7 / 2011-05-12 may not have a value set)
So ultimately I am not quite sure how to build the select part of the query to reflect a dynamic number of columns (I know it has something to do with CONCAT).
Edit --> Partial working code -->
SELECT `CategoryId`,
MAX(IF(c.`ProcessedOn` = '2011-04-20', c.`PercentChange`, NULL)) AS '2011-04-20',
MAX(IF(c.`ProcessedOn` = '2011-04-21', c.`PercentChange`, NULL)) AS '2011-04-21',
MAX(IF(c.`ProcessedOn` = '2011-04-22', c.`PercentChange`, NULL)) AS '2011-04-22',
MAX(IF(c.`ProcessedOn` = '2011-04-23', c.`PercentChange`, NULL)) AS '2011-04-23',
MAX(IF(c.`ProcessedOn` = '2011-04-24', c.`PercentChange`, NULL)) AS '2011-04-24'
FROM `Category_Gravity` c
WHERE c.`ProcessedOn` >= '2011-04-20'
AND c.`ProcessedOn` <= '2011-04-24'
GROUP BY `CategoryId`
What I need to do now is turn the
MAX(IF(c.`ProcessedOn` = '2011-04-20', c.`PercentChange`, NULL)) AS '2011-04-20',
into something more dynamic (as I the date ranges will change)
解决方案
Take a look at this similar thread where I wrote a sp to accomplish the task
edit. Updated answer
create table `pivot` (
`id` int(11) not null auto_increment,
`categoryid` int(11) default null,
`processdate` date default null,
`percentchange` int(11) default null,
primary key (`id`)
) engine=myisam auto_increment=9 default charset=latin1;
/*Data for the table `pivot` */
insert into `pivot`(`id`,`categoryid`,`processdate`,`percentchange`) values (1,4,'2011-05-10',1);
insert into `pivot`(`id`,`categoryid`,`processdate`,`percentchange`) values (2,4,'2011-05-11',22);
insert into `pivot`(`id`,`categoryid`,`processdate`,`percentchange`) values (3,4,'2011-05-12',3);
insert into `pivot`(`id`,`categoryid`,`processdate`,`percentchange`) values (4,7,'2011-05-10',4);
insert into `pivot`(`id`,`categoryid`,`processdate`,`percentchange`) values (5,7,'2011-05-11',5);
insert into `pivot`(`id`,`categoryid`,`processdate`,`percentchange`) values (6,12,'2011-05-10',6);
insert into `pivot`(`id`,`categoryid`,`processdate`,`percentchange`) values (7,12,'2011-05-12',7);
insert into `pivot`(`id`,`categoryid`,`processdate`,`percentchange`) values (8,4,'2011-05-13',12);
delimiter //
drop procedure if exists dynamic_view2//
create procedure dynamic_view2(in sdate date,in edate date)
begin
declare finish int default 0;
declare cdate date;
declare str varchar(10000) default "select categoryid,";
declare curs cursor for select processdate from pivot where processdate between sdate and edate group by processdate;
declare continue handler for not found set finish = 1;
open curs;
my_loop:loop
fetch curs into cdate;
if finish = 1 then
leave my_loop;
end if;
set str = concat(str, "max(case when processdate = '",cdate,"' then percentchange else null end) as `",cdate,"`,");
end loop;
close curs;
set str = substr(str,1,char_length(str)-1);
set @str = concat(str," from pivot
group by categoryid");
prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
end;//
delimiter ;
mysql> call dynamic_view2('2011-05-10','2011-05-13');
+------------+------------+------------+------------+------------+
| categoryid | 2011-05-10 | 2011-05-11 | 2011-05-12 | 2011-05-13 |
+------------+------------+------------+------------+------------+
| 4 | 1 | 22 | 3 | 12 |
| 7 | 4 | 5 | NULL | NULL |
| 12 | 6 | NULL | 7 | NULL |
+------------+------------+------------+------------+------------+
3 rows in set (0.00 sec)
这篇关于具有基于单列数据的动态头的MySQL数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
