如何不使用子查询、CTE和存储过程实现指定电影排序SQL查询？

表结构 #

涉及两张表：
films表结构：

id SERIAL PRIMARY KEY,
name VARCHAR(256) NOT NULL

film_actors表结构：

film_id INT,
actor_id INT,
fee INT,
UNIQUE (film_id, actor_id), INDEX (actor_id)

需求 #

按参演演员的总收入之和降序返回电影名称，具体步骤：

• 计算每位演员的总费用（sum(fee)），即演员总收入；
• 计算每部电影中所有参演演员的总收入之和，以此为依据降序排列电影名称。

示例数据 #

INSERT INTO films (id, name)
VALUES (1, 'A'), (2, 'B'), (3, 'C'), (4, 'D');

INSERT INTO film_actors (film_id, actor_id, fee)
VALUES (1, 1, 1000), (1, 2, 500), (1, 3, 3000),
       (2, 1, 500), (2, 4, 1000), (2, 5, 600),
       (3, 6, 1000), (3, 3, 100), (3, 7, 1000),
       (4, 5, 1100), (4, 7, 1200), (4, 3, 900);

预期结果：D, C, A, B

原有子查询实现 #

已通过嵌套子查询完成需求，SQL语句如下：

select
    name
from
    films
join 
    film_actors on film_actors.film_id = films.id
join  
    (select
         actor_id,
         sum(fee) as total_actor_fee
     from 
         film_actors 
     group by 
         actor_id) as total_actor_fees on total_actor_fees.actor_id = film_actors.actor_id
group by 
    1
order by 
    sum(total_actor_fee) desc

无嵌套子查询/CTE的解决方案 #

通过自连接film_actors表替代子查询，直接计算所需聚合值：

SELECT f.name
FROM films f
JOIN film_actors fa1 ON f.id = fa1.film_id
JOIN film_actors fa2 ON fa1.actor_id = fa2.actor_id
GROUP BY f.id, f.name
ORDER BY SUM(fa2.fee) DESC;

逻辑说明 #

1. 用fa1关联电影和其参演演员，确定每部电影对应的演员列表；
2. 自连接film_actors为fa2，通过actor_id关联，获取该演员所有参演记录的费用；
3. 按电影分组后，SUM(fa2.fee)即为该电影所有参演演员的总收入之和；
4. 最后按该总和降序排序，得到预期结果。

内容的提问来源于stack exchange，提问作者Dmitry Stepanov