用内部联接合并结果

debugcn 发表于 Dev

让

我写了这段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的名称不同，也必须返回

哈里德·朱奈德（M Khalid Junaid）

您缺少一个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（汇总）功能

编辑还请确保您movie_id在movies表格中确实有列

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。