mysql - SQL:选择子查询的总和

Question

我有一些包含电影、 Actor 和类别数据的相关表。我试图总结每个 Actor 在特定类别中的电影长度。

以下是我必须尝试在结果中包含类别 ID 子查询的总和:

SELECT actor.actor_id, (SELECT SUM(length) from film WHERE category_id=14) total
FROM actor
JOIN film_actor USING(actor_id)
JOIN film USING(film_id)
JOIN film_category USING(film_id)
JOIN category USING(category_id)
GROUP BY actor.actor_id
ORDER BY total DESC

但是我的 total 列只包含所有 NULL 值。

此查询有效，但不包括在此类别中观看过 0 分钟电影的 Actor :

SELECT actor.actor_id, SUM(film.length) as total
FROM actor
JOIN film_actor USING(actor_id)
JOIN film USING(film_id)
JOIN film_category USING(film_id)
JOIN category USING(category_id)
WHERE category_id = 14
GROUP BY actor.actor_id

Answer 1

您需要一个外连接，并且类别的条件属于外连接的 on 子句:

select a.actor_id,
       sum(f.length) as total
  from actor a
  join film_actor fa
    on a.actor_id = fa.actor_id
  join film f
    on fa.film_id = f.film_id
  left join film_category fc
    on fc.film_id = f.film_id
   and fc.category_id = 14
 group by a.actor_id