mysql - 选择电影名称和所有 Actor

Question

我有对电影和 Actor 进行评分的数据库。我需要选择电影的标题和所有 Actor 。我创建下一个查询:

SELECT movies.title, CONCAT_WS(' ', actors.name, actors.surname) as Actor FROM movies                LEFT JOIN movies_actors 
ON movies.id = movies_actors.movie_id 
LEFT JOIN actors 
ON actors.id = movies_actors.actor_id

我得到了一些东西:

+------------------------------+--------------+
| title                        | actor        |
+------------------------------+--------------+
| Sin City: A Dame to Kill For | Eva Green    |
| Sin City: A Dame to Kill For | Bruce Willis |
+------------------------------+--------------+

但我想要得到这样的东西:

+------------------------------+--------------+
| title                        | actor        |
+------------------------------+--------------+
| Sin City: A Dame to Kill For | Eva Green    |
|                              | Bruce Willis |
+------------------------------+--------------+

我尝试使用

SELECT DISTINCT movies.title... 

但这没有帮助。

你能帮我吗？谢谢!这是我的数据库

CREATE TABLE movies ( 
    id int NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    title VARCHAR(255),
    director VARCHAR(255), 
    created DATE, 
    budget DECIMAL(12,2), 
    box_office DECIMAL(13,2), 
    duration TIME 
)

 CREATE TABLE actors (
   id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
   surname VARCHAR(255),
   name VARCHAR(255),
   birthday DATE
 )

ALTER TABLE movies_actors
  ADD FOREIGN KEY (movie_id) REFERENCES movies (id)
ALTER TABLE movies_actors
  ADD FOREIGN KEY FOREIGN KEY (actor_id) REFERENCES actors (id)

INSERT INTO 'movies' 
    (`id`, `title`, `director`, `created`, `budget`, `box_office`, `duration`) 
VALUES 
    (1, 'Sin City: A Dame to Kill For', 'Frank Miller, Robert Anthony Rodriguez',                          '2014', 62000000.00, 38000000.00, '01:42:00')

INSERT INTO `actors` 
    (`id`, `surname`, `name`, `birthday`) 
VALUES
    (1, 'Green', 'Eva', '1980-07-06'),
    (2, 'Willis', 'Bruce', '1955-03-19')

Answer 1

GROUP_CONCAT()怎么样

SELECT movies.title, 
       GROUP_CONCAT(concat(actors.name, ' ', actors.surname)) as Actors
FROM movies                
LEFT JOIN movies_actors ON movies.id = movies_actors.movie_id 
LEFT JOIN actors ON actors.id = movies_actors.actor_id
GROUP BY movies.title