select s.id,s.name,max(case when e.course_id = 55 then complete else null end) as c55,max(case when e.course_id = 66 then complete else null end) as c66,max(case when e.course_id = 77 then complete else null end) as c77from student as sleft join enrollment as eon s.id = e.student_idgroup by s.id
@克里斯。使用存储过程，您甚至可以在不知道列数的情况下创建动态数据透视表。这是链接
http://forum.html.it/forum/showthread.php?s=&threadid=1456236
我在一个意大利论坛上对类似问题的回答。有一个完整的示例可以帮助您了解背后的逻辑。:)
编辑。 使用MYSQL动态视图进行更新
这是我的开始转储：
drop table if exists `student`;create table `student` ( `id` int(10) unsigned not null auto_increment, `name` varchar(50) default null, primary key (`id`)) engine=myisam;insert into `student`(`id`,`name`) values (1,'chris');insert into `student`(`id`,`name`) values (2,'joe');insert into `student`(`id`,`name`) values (3,'jack');drop table if exists enrollment;create table `enrollment` ( `enrollment_id` int(11) auto_increment primary key, `student_id` int(11) default null, `course_id` int(11) default null, `complete` varchar(50) default null) engine=myisam auto_increment=8 default charset=latin1;insert into `enrollment`(`enrollment_id`,`student_id`,`course_id`,`complete`) values (1,1,55,'true');insert into `enrollment`(`enrollment_id`,`student_id`,`course_id`,`complete`) values (2,1,66,'true');insert into `enrollment`(`enrollment_id`,`student_id`,`course_id`,`complete`) values (3,1,77,'true');insert into `enrollment`(`enrollment_id`,`student_id`,`course_id`,`complete`) values (4,2,55,'true');insert into `enrollment`(`enrollment_id`,`student_id`,`course_id`,`complete`) values (5,2,66,'false');insert into `enrollment`(`enrollment_id`,`student_id`,`course_id`,`complete`) values (6,3,55,'false');insert into `enrollment`(`enrollment_id`,`student_id`,`course_id`,`complete`) values (7,3,66,'true');
这是动态视图的存储过程：
delimiter //drop procedure if exists dynamic_view//create procedure dynamic_view()begindeclare finish int default 0;declare cid int;declare str varchar(10000) default "select s.id,s.name,";declare curs cursor for select course_id from enrollment group by course_id;declare continue handler for not found set finish = 1;open curs;my_loop:loopfetch curs into cid;if finish = 1 thenleave my_loop;end if;set str = concat(str, "max(case when e.course_id = ",cid," then complete else null end) as course_",cid,",");end loop;close curs;set str = substr(str,1,char_length(str)-1);set @str = concat(str," from student as s left join enrollment as e on s.id = e.student_id group by s.id");prepare stmt from @str;execute stmt;deallocate prepare stmt;-- select str;end;//delimiter ;
现在叫它
mysql> call dynamic_view();+----+-------+-----------+-----------+-----------+| id | name | course_55 | course_66 | course_77 |+----+-------+-----------+-----------+-----------+| 1 | chris | true | true | true || 2 | joe | true | false | NULL || 3 | jack | false | true | NULL |+----+-------+-----------+-----------+-----------+3 rows in set (0.00 sec)Query OK, 0 rows affected (0.05 sec)
现在，我们插入另外两个具有两个不同课程的记录：
insert into `enrollment`(`student_id`,`course_id`,`complete`) values (1,88,'true');insert into `enrollment`(`student_id`,`course_id`,`complete`) values (3,99,'true');
我们回想一下程序。结果如下：
mysql> call dynamic_view();+----+-------+-----------+-----------+-----------+-----------+-----------+| id | name | course_55 | course_66 | course_77 | course_88 | course_99 |+----+-------+-----------+-----------+-----------+-----------+-----------+| 1 | chris | true | true | true | true | NULL || 2 | joe | true | false | NULL | NULL | NULL || 3 | jack | false | true | NULL | NULL | true |+----+-------+-----------+-----------+-----------+-----------+-----------+3 rows in set (0.00 sec)Query OK, 0 rows affected (0.02 sec)
就这样。:)
