mysql 展平各列_将两个表（具有1-M关系）联接在一起，其中第二个表需要“展平”到一行...

小编典典

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

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

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

编辑。 使用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)

就这样。:)

2020-05-17