我正在尝试编写查询以创建数据的“表”，如下所示：
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查询，而不是由应用程序操纵）：
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可能为null（假设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以完成任务
将两个表（具有1-M关系）联接在一起，其中第二个表需要“展平”到一行
编辑。 更新的答案
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)
