mysql - 为每个客户 X 写一个查询，另一个客户 Y 与 X 租了至少一部电影

Question

使用 sakila 数据库，编写一个查询，为每个客户 X 找到另一个客户 Y，该客户至少与 X 共同租借了一部电影。找到所有这样的客户对 (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。

标签： mysqlsql

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;