mysql - 连接结果与内部连接

Question

我写了这段 SQL 来获取带有角色的电影(role_id = 1 - 是导演)。虽然电影只有一位导演 - 一切都很好，但当有多位导演时，会出现具有相同电影名称的额外行。

SELECT
    m.name, 
    ps.name as director
FROM
    movies m
INNER JOIN participants p ON
    m.movie_id = p.movie_id
INNER JOIN persones ps ON
    ps.person_id = p.person_id AND
    p.role_id = 1    
ORDER BY m.updated_at DESC
LIMIT 10\G

例如

*************************** 1. row ***************************
    name: Film1
director: director1
*************************** 2. row ***************************
    name: Film1
director: director2
*************************** 3. row ***************************
    name: Film1
director: director3
*************************** 4. row ***************************
    name: Film2
director: director4
*************************** 5. row ***************************
    name: Film2
director: director5
*************************** 6. row ***************************
    name: Film2
director: director6

好的，我发现 group_concat 用于在单行中连接董事

SELECT
    m.name, 
    GROUP_CONCAT(ps.name SEPARATOR ", ") as director
FROM
    movies m
INNER JOIN participants p ON
    m.movie_id = p.movie_id
INNER JOIN persones ps ON
    ps.person_id = p.person_id AND
    p.role_id = 1    
ORDER BY m.updated_at DESC
LIMIT 10\G

但我有意想不到的结果!

*************************** 1. row ***************************
    name: Film123
director: director1, someperson1, someperson3, ......, someperson18

只有一排来自另一类别的人(艺术家或其他人)。即使电影行的名称与 ORDER BY m.updated_at DESC 必须返回

Answer 1

您缺少 GROUP BY 您正在使用组函数但没有对它们进行分组

SELECT
    m.name, 
    GROUP_CONCAT(ps.name SEPARATOR ", ") as director
FROM
    movies m
INNER JOIN participants p ON
    m.movie_id = p.movie_id
INNER JOIN persones ps ON
    ps.person_id = p.person_id AND
    p.role_id = 1    
GROUP BY m.name /* or you can use the movie id column here*/
ORDER BY m.updated_at DESC
LIMIT 10

GROUP_CONCAT(expr)

GROUP BY (Aggregate) Functions

编辑 同时确保您的 movies 表中有 movie_id 列

SELECT
    m.movie_id ,
    m.international_name AS o_name,
    m.description AS description,
    m.name AS name,
    m.covers AS covers,
    m.YEAR AS YEAR,
    m.updated_at AS added,
    GROUP_CONCAT(ps.name SEPARATOR ', ') AS director
FROM
    movies AS m
INNER JOIN  movies_genres AS mgen ON
    (m.movie_id = mgen.movie_id)
INNER JOIN participants p ON
    m.movie_id = p.movie_id
INNER JOIN persones ps ON
    ps.person_id = p.person_id AND
    p.role_id = 1 /* Directors */
WHERE
    m.hidden=0 AND
    mgen.genre_id = 6 AND

GROUP BY m.name
ORDER BY m.updated_at DESC