mysql - 为每个客户 X 编写一个查询，另一位客户 Y 至少租借了一部与 X 共同的电影

Question

使用 sakila 数据库，编写一个查询，为每个客户 X 查找另一个与 X 共同租借了至少一部电影的客户 Y。找到所有这样的客户对 (X, Y)，并针对每一对，重叠电影的数量。按重叠电影的数量对结果进行排序

我尝试过使用别名、内部联接和子查询。但是，我相信我的代码存在语法错误。

SELECT o1.customer_id AS CustomerID1,
o2.customer_id AS CustomerID2,
COUNT(*) NoOfOverlappingMovies
FROM( ( (SELECT c.customer_id, f.film_id
        FROM customer AS c,
        JOIN rental AS r
        ON r.customer_id = c.customer_id)
        JOIN inventory AS i ON i.inventory_id = r.inventory_id)
        JOIN film AS f ON i.film_id = f.film_id
        ) AS o1
JOIN( ( (SELECT c.customer_id, f.film_id
        FROM customer AS c,
        JOIN rental AS r
        ON r.customer_id = c.customer_id)
        JOIN inventory AS i ON i.inventory_id = r.inventory_id)
        JOIN film AS f ON i.film_id = f.film_id
        ) AS o2
ON o2.film_id = o1.film_id AND o2.customer_id < o1.customer_id
GROUP BY o1.customer_id, o2.customer_id
ORDER BY COUNT(*) DESC;

查询应有 3 列。 CustomerID1、CustomerID2 和 NoOfOverlappingMovies。

Answer 1

1) 不要在“FROM”和“JOIN”部分之间使用“,”。

2)你的括号有点不对劲。我尝试在没有表格的情况下尽可能地纠正它们:

SELECT o1.customer_id AS CustomerID1,
       o2.customer_id AS CustomerID2,
       COUNT(*) NoOfOverlappingMovies
FROM( (SELECT c.customer_id, f.film_id
        FROM customer AS c
        JOIN rental AS r ON r.customer_id = c.customer_id
        JOIN inventory AS i ON i.inventory_id = r.inventory_id
        JOIN film AS f ON i.film_id = f.film_id
        ) AS o1
        JOIN (SELECT c.customer_id, f.film_id
                FROM customer AS c
                JOIN rental AS r ON r.customer_id = c.customer_id
                JOIN inventory AS i ON i.inventory_id = r.inventory_id
                JOIN film AS f ON i.film_id = f.film_id
    ) AS o2 ON o2.film_id = o1.film_id AND o2.customer_id < o1.customer_id )
GROUP BY o1.customer_id, o2.customer_id
ORDER BY COUNT(*) DESC;