PostgreSQL内连接后合并同一电影的演职人员列

我创建了my_movie、person_name、role、movie_employee四张电影相关数据库表并插入数据，执行内连接查询后，同一电影的演职人员各占一行，现需将同一电影的所有演职人员信息合并到一行，如何实现？

相关建表、插入数据及原始查询代码如下：

CREATE TABLE my_movie
        (movie_id int not null,
         movie_name char(100) not null,
        movie_released date not null,
        primary key(movie_id))
CREATE TABLE person_name
        (person_id int primary key,
        first_name char(50)not null,
        last_name char(50) not null,
        birthdate date)
CREATE TABLE role
        (role_id int primary key,
        role_name char(50) not null)
CREATE TABLE movie_employee
        (movie_id int not null,
        person_id int not null,
        role_id int not null,
        primary key(movie_id, person_id, role_id),
        FOREIGN KEY(movie_id) references my_movie(movie_id),
        FOREIGN KEY(person_id) references person_name(person_id),
        FOREIGN KEY(role_id) references role(role_id))

insert into role VALUES(1, 'director');
insert into role VALUES(2, 'actor');
insert into role VALUES(3, 'actress');

insert into person_name values(1, 'James', 'Cameron', '1954-8-16');
insert into person_name values(2, 'Leonardo', 'DiCaprio');
insert into person_name values(3, 'Kate', 'Winslet');
insert into person_name values(4, 'Francis', 'Coppola');
insert into person_name values(5, 'Marlon', 'Brando');
insert into person_name values(6, 'Keanu', 'Reeves');
insert into person_name values(7, 'Hugo', 'Weaving');

insert into my_movie VALUES(1, 'Titanic', '1997-12-19');
insert into my_movie VALUES(2, 'The Godfather', '1972-03-15');
insert into my_movie VALUES(3, 'The Matrix', '1999-03-31');

insert into movie_employee VALUES(1, 1, 1);
insert into movie_employee VALUES(1, 2, 2);
insert into movie_employee VALUES(1, 3, 3);
insert into movie_employee VALUES(2, 4, 1);
insert into movie_employee VALUES(2, 5, 2);
insert into movie_employee VALUES(3, 6, 2);
insert into movie_employee VALUES(3, 7, 2);

原始查询语句：

SELECT movie_name, movie_released, (role_name || ': ' || first_name || ' ' || last_name)as "Cast & Crew"
from my_movie
inner join movie_employee on my_movie.movie_id=movie_employee.movie_id
inner join role on movie_employee.role_id = role.role_id
inner join person_name on movie_employee.person_id = person_name.person_id

该查询结果中同一电影重复多行，每个演职人员单独一行，需合并为单行展示所有演职人员信息。

要实现同一电影的演职人员信息合并为单行，核心是使用字符串聚合函数结合GROUP BY按电影分组。以下是主流数据库的实现方式：

1. MySQL / MariaDB #

使用GROUP_CONCAT函数，支持自定义分隔符：

SELECT 
    m.movie_name, 
    m.movie_released,
    GROUP_CONCAT(
        CONCAT(r.role_name, ': ', p.first_name, ' ', p.last_name)
        SEPARATOR '; '
    ) AS "Cast & Crew"
FROM my_movie m
INNER JOIN movie_employee me ON m.movie_id = me.movie_id
INNER JOIN role r ON me.role_id = r.role_id
INNER JOIN person_name p ON me.person_id = p.person_id
GROUP BY m.movie_id, m.movie_name, m.movie_released;

2. PostgreSQL #

使用STRING_AGG函数：

SELECT 
    m.movie_name, 
    m.movie_released,
    STRING_AGG(
        r.role_name || ': ' || p.first_name || ' ' || p.last_name,
        '; '
    ) AS "Cast & Crew"
FROM my_movie m
INNER JOIN movie_employee me ON m.movie_id = me.movie_id
INNER JOIN role r ON me.role_id = r.role_id
INNER JOIN person_name p ON me.person_id = p.person_id
GROUP BY m.movie_id, m.movie_name, m.movie_released;

3. Oracle #

使用LISTAGG函数，支持指定排序规则：

SELECT 
    m.movie_name, 
    m.movie_released,
    LISTAGG(
        r.role_name || ': ' || p.first_name || ' ' || p.last_name,
        '; '
    ) WITHIN GROUP (ORDER BY r.role_name) AS "Cast & Crew"
FROM my_movie m
INNER JOIN movie_employee me ON m.movie_id = me.movie_id
INNER JOIN role r ON me.role_id = r.role_id
INNER JOIN person_name p ON me.person_id = p.person_id
GROUP BY m.movie_id, m.movie_name, m.movie_released;

4. SQL Server 2017+ #

使用STRING_AGG函数：

SELECT 
    m.movie_name, 
    m.movie_released,
    STRING_AGG(
        CONCAT(r.role_name, ': ', p.first_name, ' ', p.last_name),
        '; '
    ) AS "Cast & Crew"
FROM my_movie m
INNER JOIN movie_employee me ON m.movie_id = me.movie_id
INNER JOIN role r ON me.role_id = r.role_id
INNER JOIN person_name p ON me.person_id = p.person_id
GROUP BY m.movie_id, m.movie_name, m.movie_released;

5. SQL Server 2016及更早版本 #

使用STUFF结合FOR XML PATH的兼容方案：

SELECT 
    m.movie_name, 
    m.movie_released,
    STUFF(
        (
            SELECT '; ' + r.role_name + ': ' + p.first_name + ' ' + p.last_name
            FROM movie_employee me
            INNER JOIN role r ON me.role_id = r.role_id
            INNER JOIN person_name p ON me.person_id = p.person_id
            WHERE me.movie_id = m.movie_id
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'),
        1, 2, ''
    ) AS "Cast & Crew"
FROM my_movie m
GROUP BY m.movie_id, m.movie_name, m.movie_released;

关键说明 #

• 所有方案通过GROUP BY按电影唯一标识（movie_id）及展示字段分组，确保同一电影仅输出一行。
• 分隔符可按需修改，比如用换行符CHAR(10)或逗号, 替代示例中的; 。
• Oracle的LISTAGG支持ORDER BY子句，可自定义演职人员的排序逻辑。

内容的提问来源于stack exchange，提问作者Joe Lee