SQL视图创建查询持续运行无响应 陷入循环无法终止问题问询

该SQL无法执行完成的核心原因是关联条件错误产生了巨量笛卡尔积，具体问题点如下：

• 子查询h是对payment表按rental_id分组，统计每个租赁订单的总支付金额，但你关联h时使用的条件ON a.customer_id = d.customer_id是恒成立的——因为你已经通过d.customer_id = a.customer_id关联了customer表，相当于每条主查询的租赁记录都会和子查询h返回的所有记录匹配，数据量指数级膨胀，导致语句一直运行无法结束。
• 额外存在一处语法隐患：JOIN category AS g ON g.category_id = f. category_id中f.后多了空格，部分数据库会直接抛出语法错误。

DROP VIEW IF EXISTS rental_details;
CREATE VIEW rental_details AS
SELECT a.rental_id, a.rental_date, a.return_date,
    b.film_id, b.store_id, 
    c.first_name AS staff_name, 
    d.first_name || ' ' || d.last_name AS customer_name,
    e.title, e.rental_duration, e.rental_rate, e.length, e.replacement_cost, e.rating,
    g.name AS category_name,
    h.total_amount AS amount_paid
FROM rental AS a 
JOIN inventory AS b ON b.inventory_id = a.inventory_id 
JOIN staff AS c ON c.staff_id = a.staff_id
JOIN customer AS d ON d.customer_id = a.customer_id
JOIN film AS e ON e.film_id = b.film_id 
JOIN film_category AS f ON f.film_id = e.film_id 
JOIN category AS g ON g.category_id = f.category_id
-- 修正关联条件，按子查询的聚合维度rental_id匹配
LEFT JOIN (select rental_id, sum(amount) as total_amount 
from payment 
group by rental_id) as h ON a.rental_id = h.rental_id;

如果修复后执行效率仍不符合预期，可以做以下优化：

• 给payment表的rental_id字段增加索引，加速子查询的分组聚合操作
• 给各表关联用到的外键字段增加索引，比如rental.inventory_id、inventory.film_id等，提升JOIN效率

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