mysql - 如何在 MySQL 中使用多个 JOINS 和 GROUP_CONCAT 输出包含空白字段的行？

Question

我在尝试编写的 MySQL 查询中的 JOINS 或 GROUP_CONCAT 函数遇到问题。我有一个数据库，其中包含下表和足够的示例行来帮助别人回答我的问题:

类型转换-

actor_id [PK] | actor_name
--------------+--------------
ryan-reynolds | Ryan Reynolds

女类型转换-

actress_id [PK]    | actress_name
-------------------+-------------------
blake-lively       | Blake Lively
morena-baccarin    | Morena Baccarin
brianna-hildebrand | Brianna Hildebrand

工作室 table -

studio_id [PK]        | studio_name
----------------------+----------------------
twentieth-century-fox | Twentieth Century Fox
warner-bros           | Warner Bros.
audiovisual-aval-sgr  | Audiovisual Aval SGR

电影表 -

movie_id [PK] | movie_title   | studio_id [FK]        | movie_date 
--------------+---------------+-----------------------+---------------
1             | Deadpool      | twentieth-century-fox | 2016-02-12
2             | Green Lantern | warner-bros           | 2011-06-17
3             | Buried        | audiovisual-aval-sgr  | 2010-10-15

movie_actors 表 -

actor_id [FK] | movie_id [FK]
--------------+--------------
ryan-reynolds | 1
ryan-reynolds | 2
ryan-reynolds | 3

movie_actresses 表 -

actress_id [FK]    | movie_id [FK]
-------------------+--------------
blake-lively       | 2
morena-baccarin    | 1
brianna-hildebrand | 1

我试图编写的 MySQL 查询是返回特定 Actor “Ryan Reynolds”的电影列表，以及电影的发行日期、工作室，然后是也出演该电影的女 Actor 的串联(如果有的话)。我编写了以下查询:

SELECT
    movie_date,
    movie_title,
    studio_name,
    GROUP_CONCAT(actress_name SEPARATOR ", ") AS all_actress_names
FROM
    movie
NATURAL JOIN studio NATURAL JOIN movie_actors NATURAL JOIN movie_actresses NATURAL JOIN actress WHERE actor_id = "ryan-reynolds"
GROUP BY
    movie_id
ORDER BY
    movie_date
DESC

但它不会返回电影“Buried”，因为我没有任何与该电影相关的女 Actor 。输出如下:

movie_date | movie_title   | studio_name           | all_actress_names
-----------+---------------+-----------------------+------------------------------------
2016-02-12 | Deadpool      | Twentieth Century Fox | Morena Baccarin, Brianna Hildebrand
2011-06-17 | Green Lantern | Warner Bros.          | Blake Lively

如何编辑查询以返回所有电影？将“埋葬”的“all_actress_names”字段留空，如下所示:

movie_date | movie_title   | studio_name           | all_actress_names
-----------+---------------+-----------------------+------------------------------------
2016-02-12 | Deadpool      | Twentieth Century Fox | Morena Baccarin, Brianna Hildebrand
2011-06-17 | Green Lantern | Warner Bros.          | Blake Lively
2010-10-15 | Buried        | Audiovisual Aval SGR  |

我想我可能使用了错误的连接。但也可能是因为 GROUP_CONCAT 返回 NULL。我尝试过在“actress_name”字段上使用 ISNULL、IFNULL 和 COALESCE，但没有成功。

Answer 1

1. 首先，您需要使用左连接将查询链接到女 Actor ，因为女 Actor 的数量可能为零。
2. 其次，您需要按照您所说的使用 IFNULL 或 COALESCE 来管理 NULL 值。

请参阅以下查询:

SELECT
    movie_date,
    movie_title,
    studio_name,
    GROUP_CONCAT(IFNULL(actress_name,'') ORDER BY actress_name SEPARATOR ", ") AS all_actress_names
FROM
    movie M
NATURAL JOIN studio S
NATURAL JOIN movie_actors MAM
LEFT JOIN movie_actresses MAW ON MAW.movie_id = M.movie_id
LEFT JOIN actress AW ON MAW.actress_id = AW.actress_id
WHERE 
    actor_id = "ryan-reynolds"
GROUP BY
    M.movie_id
ORDER BY
    movie_date
DESC

SEE DEMO HERE