SQL 精选演员一起玩

Question

演员/电影查询有很多变化。而且当尝试从非组列中进行选择时，我找不到正确的方法： 我有3张桌子电影(film_id，标题(，演员(actor_id，first_name，last_name(，film_actor(actor_id，film_id(。所以，我需要找2个演员，他们一起玩得最多，并为前5部这样的电影actor1_last_name、actor2_last_name film_title显示输出。我做的是，首先，算演员的颜值：

select r1.actor_id as actor_a, r2.actor_id as actor_b,
count(r1.film_id) as casted_together
from film_actor r1 inner join film_actor r2 on r1.film_id = r2.film_id
and r1.actor_id > r2.actor_id
group by r1.actor_id, r2.actor_id
order by casted_together desc

这让我返回了这样的表格

actor_a | actor_b| casted_together
Name      Name      7
...       ...       6

但是一旦我尝试添加 film.title 来选择它，它就会说我不能使用它，以防它不在组部分 ((select title from film where film_id = r1.film_id) as film_title(。subquery uses ungrouped column "r1.film_id" from outer query

如何使用上述分组的结果并在那里添加一些外列？就我而言，它是film.title.

期望输出：

actor_a_last_name | actor_b_last_name| film_title
Name                Name                Title
...                 ...                 ...

比如说，乔治克鲁尼和面包皮特在一起的电影数量最多(9( 结果应显示 5 行中的前 9 行

actor_a_last_name | actor_b_last_name| film_title
Clooney                Pitt            Film 1
Clooney                Pitt            Film 2
...                    ...             ...

Answer 1

使用聚合，例如string_agg()或array_agg()：

select 
r1.actor_id as actor_a, 
r2.actor_id as actor_b,
count(r1.film_id) as casted_together,
string_agg(f.title, ', ') as films
from film_actor r1 
join film_actor r2 
on r1.film_id = r2.film_id
and r1.actor_id > r2.actor_id
join film f
on r1.film_id = f.film_id
group by r1.actor_id, r2.actor_id
order by casted_together desc

将查询用作派生表，并添加演员姓名和电影标题：

select 
a1.last_name,
a2.last_name,
f.title
from (
select 
r1.actor_id as actor_a_id, 
r2.actor_id as actor_b_id,
count(r1.film_id) as casted_together,
array_agg(r1.film_id) as film_ids
from film_actor r1 
join film_actor r2 
on r1.film_id = r2.film_id
and r1.actor_id > r2.actor_id
join film f
on r1.film_id = f.film_id
group by r1.actor_id, r2.actor_id
order by casted_together desc
limit 1
) s
join actor a1 on a1.actor_id = s.actor_a_id
join actor a2 on a2.actor_id = s.actor_b_id
join film f on f.film_id = any(s.film_ids)