给出下表：
学生
+----+-------+ | id | Name | +----+-------+ | 1 | Chris | | 2 | Joe | | 3 | Jack | +----+-------+
注册
+---------------+------------+-----------+----------+ | enrollment_id | student_id | course_id | complete | +---------------+------------+-----------+----------+ | 1 | 1 | 55 | true | | 2 | 1 | 66 | true | | 3 | 1 | 77 | true | | 4 | 2 | 55 | true | | 5 | 2 | 66 | false | | 6 | 3 | 55 | false | | 7 | 3 | 66 | true | +---------------+------------+-----------+----------+
我想要以下
+----+-------+-----------+-----------+-----------+ | id | Name | Course 55 | Course 66 | Course 77 | +----+-------+-----------+-----------+-----------+ | 1 | Chris | true | true | true | | 2 | Joe | true | false | NULL | | 3 | Jack | false | true | NULL | +----+-------+-----------+-----------+-----------+
注意1： 我知道mysql不能有动态列（如果我错了，请纠正我！），因此我对查询的开头感到满意：
SELECT id, name, course_55, course_66, course_77 etc...
我对此感到满意，因为课程的数量是固定的（准确地说是4门）。 理想情况下， 我希望它是动态的。也就是说，不必手动在SELECT子句中编写每个课程。
注意2： 这需要纯mysql-我不想求助于PHP。
该数据库目前可容纳10000多名学生，并具有10000 + * 4个注册（因为正好有4门课程，每个学生都在全部4个模块中）。
注意3： Student.user_id和enrollment.enrollment_id，enrollment.student_id和enrollment.course_id也已建立索引。
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 c77 from student as s left join enrollment as e on s.id = e.student_id group by s.id
@克里斯。使用存储过程，您甚至可以在不知道列数的情况下创建动态数据透视表。这是链接
http://forum.html.it/forum/showthread.php?s=&threadid=1456236
我在一个意大利论坛上对类似问题的回答。有一个完整的示例可以帮助您了解背后的逻辑。:)
编辑。 使用MYSQL动态视图进行更新
这是我的开始转储：
/*Table structure for table `student` */ 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; /*Data for the table `student` */ 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; /*Data for the table `enrollment` */ 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() begin declare 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:loop fetch curs into cid; if finish = 1 then leave 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)
就这样。:)
