连接两个表(具有 1-M 关系)，其中第二个表需要“展平"为一行 [英] Join two tables (with a 1-M relationship) where the second table needs to be 'flattened' into one row

问题描述

给定以下表格:

学生

+----+-------+
| 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 不能有动态列(如果我错了，请纠正我！)所以我对查询开始感到满意:

Note 1: I know mysql can't have dynamic columns (correct me if I'm wrong!) so I am happy with the query starting as:

SELECT id, name, course_55, course_66, course_77 etc...

我对此很满意，因为课程数量是固定的(准确地说是 4 门).理想情况下我希望它是动态的；也就是说，不必在 SELECT 子句中手动编写每个课程.

I happy with this because there is a fixed number of courses (4 to be exact). Ideally I would want it to be dynamic; that is, not having to manually write each course in the SELECT clause.

注意 2: 这需要 mysql 纯 - 我不想诉诸 PHP.

Note 2: This needs to mysql pure - I don't want to resort to PHP.

该数据库目前有 10000+ 名学生，注册人数为 10000+ * 4(因为正好有 4 门课程，每个学生都在所有 4 个模块中).

The database currently stands at 10000+ students with 10000+ * 4 enrollments (as there is exactly 4 courses, and every student is in all 4 modules).

注意 3:Student.user_id 已编入索引，enrollment.enrollment_id、enrollment.student_id 和enrollment.course_id 也已编入索引.

Note 3: Student.user_id is indexed and so is enrollment.enrollment_id, enrollment.student_id, and 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

@克里斯.使用存储过程，您甚至可以在不知道列数的情况下创建动态数据透视表.这是链接

@Chris. Using stored procedure you could even create dynamic pivot table without knowing before the number of columns. This is the link

http://forum.html.it/forum/showthread.php?s=&threadid=1456236

我在意大利论坛上对类似问题的回答.有一个完整的例子可以帮助你理解背后的逻辑.:)

of an answer of mine on an italian forum to a similar problem. There is a complete example that could help you to understand the logic behind. :)

编辑.使用 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');

这是动态视图的存储过程:

and this is the stored procedure for the dynamic view:

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)

现在我们插入另外两条具有两个不同课程的记录:

Now we insert other two records with two different courses:

insert  into `enrollment`(`student_id`,`course_id`,`complete`) values (1,88,'true');
insert  into `enrollment`(`student_id`,`course_id`,`complete`) values (3,99,'true');

我们回忆一下这个过程.结果如下:

and we recall the procedure. This is the result:

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)

仅此而已.:)

这篇关于连接两个表(具有 1-M 关系)，其中第二个表需要“展平"为一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！