SQL如何创建新表并实现自连接？相关查询习题求解

前提说明 #

默认基于经典Sakila影视租赁示例数据库实现，涉及核心表结构如下：

• customer：客户信息表，主键为customer_id
• rental：租赁记录表，关联customer_id与库存表主键inventory_id
• inventory：库存信息表，关联inventory_id与电影表主键film_id
• film_actor：电影-演员关联表，关联film_id与演员表主键actor_id

如果你使用的是自定义表结构，只要对应调整字段和关联逻辑，核心实现思路通用。

核心实现思路 #

1. 先构建客户-演员关联映射表：关联全链路租赁表，拿到每个客户租过的所有电影对应的全部演员
2. 对关联表做自连接：匹配相同演员ID，过滤客户X与客户Y为同一人的无效数据
3. 按客户对分组统计去重后的重叠演员数量，按数量排序输出

实现代码 #

方案1：临时表+自连接（符合你提到的「先创建新表再自连接」的要求） #

-- 1. 创建临时表存储客户与对应看过的演员的关联关系，去重避免重复计数
CREATE TEMPORARY TABLE customer_actor AS
SELECT DISTINCT 
    c.customer_id,
    fa.actor_id
FROM customer c
JOIN rental r ON c.customer_id = r.customer_id
JOIN inventory i ON r.inventory_id = i.inventory_id
JOIN film_actor fa ON i.film_id = fa.film_id;

-- 2. 自连接临时表统计重叠演员数量
SELECT 
    ca1.customer_id AS customer_x,
    ca2.customer_id AS customer_y,
    COUNT(DISTINCT ca1.actor_id) AS overlap_actor_count
FROM customer_actor ca1
INNER JOIN customer_actor ca2 
    ON ca1.actor_id = ca2.actor_id -- 匹配相同出演演员
    AND ca1.customer_id < ca2.customer_id -- 过滤X=Y的无效对，同时避免重复生成(X,Y)和(Y,X)两组相同结果
GROUP BY ca1.customer_id, ca2.customer_id
ORDER BY overlap_actor_count DESC;

可选参数调整 #

如果题目要求保留双向客户对（即同时输出(1,2)和(2,1)两组数据），把自连接条件里的ca1.customer_id < ca2.customer_id替换为ca1.customer_id != ca2.customer_id即可。

方案2：CTE公共表表达式实现（无需创建临时表，代码更简洁） #

WITH customer_actor AS (
    SELECT DISTINCT 
        c.customer_id,
        fa.actor_id
    FROM customer c
    JOIN rental r ON c.customer_id = r.customer_id
    JOIN inventory i ON r.inventory_id = i.inventory_id
    JOIN film_actor fa ON i.film_id = fa.film_id
)
SELECT 
    ca1.customer_id AS customer_x,
    ca2.customer_id AS customer_y,
    COUNT(DISTINCT ca1.actor_id) AS overlap_actor_count
FROM customer_actor ca1
INNER JOIN customer_actor ca2 
    ON ca1.actor_id = ca2.actor_id
    AND ca1.customer_id < ca2.customer_id
GROUP BY ca1.customer_id, ca2.customer_id
ORDER BY overlap_actor_count DESC;

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