MySQL - 嵌套结果连接

Question

我有 3 个表(来自 sakila 示例数据库):film、film_actor 和 actor。

Film: (1000 rows)film_id | title | ....______________________1       | a     | ....2       | b     | ....3       | c     | ....4       | d     | ....5       | e     | ..........................

Film_actor:(5000+ rows)actor_id | film_id | ....______________________1        | 12      | ....1        | 33      | ....2        | 210     | ....3        | 2       | ....3        | 500     | ..........................

  Actor:(~200 rows)  actor_id | first_name  | last_name__________________________________1        | a           | f2        | b           | g3        | c           | h4        | d           | i5        | e           | l...............................

有没有办法得到这样的结果:

  title(film)       | actors(first_name, last_name) _________________________________________________________________________title 1           | first_name1 last_name1, first_name2 last_name2, etc..         title 2           | first_name1 last_name1, first_name2 last_name2, etc..title 3           | first_name1 last_name1, first_name2 last_name2, etc..title 4           | first_name1 last_name1, first_name2 last_name2, etc..title 5           | first_name1 last_name1, first_name2 last_name2, etc...........................................................................

即获取按 ID 排序的电影列表，将所有在电影中扮演的 Actor 放入第 2 列。
我已经检查过，但由于嵌套连接，Stack Overflow 上似乎没有答案符合我的问题。
我只需要通过 MySQL 查询获得此结果。

Answer 1

select `title`, group_concat(concat(first_name, ' ', last_name)) as actors
from film inner join film_actor on film.film_id = film_actor.`film_id`
inner join actor on film_actor.actor_id = actor.actor_id
group by `title`