我必须从sql运行查询.也许太容易或不容易,但不幸的是我不懂sql.所以这是我的表：
影片
id | movie_name ------------ 1 | prometheus
名
id | person_name ----------------- 1 | Ridley Scott 2 | Noomi Rapace 3 | Michael Fassbender 4 | Charlize Theron 5 | Damon Lindelof
films_cast
id | film_id | person_id | type ------------------------------- 1 | 1 | 1 | director 2 | 1 | 2 | actor 3 | 1 | 3 | actor 4 | 1 | 4 | actor 5 | 1 | 5 | writer
我怎样才能从这些表中得出这样的结论：
film_archive
id | movie_name | director | writer | cast --------------------------------------------------------------------------------------- 1 | prometheus | Ridley Scott | Damon Lindelof | Noomi Rapace,Michael Fassbender,Charlize Theron
谢谢！
您需要
join表,然后在给定合适条件的情况下按名称列执行
GROUP_CONCAT()聚合的电影分组：
SELECT f.id,f.movie_name,GROUP_CONCAT(IF(c.type='director',n.person_name,NULL)) AS director,GROUP_CONCAT(IF(c.type='writer',NULL)) AS writer,GROUP_CONCAT(IF(c.type='actor',NULL)) AS cast
FROM films f LEFT JOIN films_cast c ON c.film_id = f.id
LEFT JOIN names n ON c.person_id = n.id
GROUP BY f.id
在sqlfiddle上看到它.
