mysql - "longest film rental duration and the customer who rented the said film"MySQL Sakila 查询

Question

我正在尝试编写一个查询，该查询将返回 Sakila 数据库中最长的电影租赁持续时间以及租赁该电影(以及电影的标题)的客户。

首先，我编写了以下查询，该查询应该返回 film_id、inventory_id、customer_id 和最长租赁期限(以周为单位):

SELECT DDIFF.*

FROM (SELECT rental.inventory_id, rental.customer_id, DATEDIFF(rental.return_date, rental.rental_date)/7 AS TM FROM rental) AS DDIFF

INNER JOIN  (SELECT  DDIFF.inventory_id, DDIFF.customer_id, MAX(DDIFF.TM) AS WEEKS 
FROM (SELECT  rental.inventory_id, rental.customer_id, DATEDIFF(rental.return_date, rental.rental_date)/7 AS TM FROM rental) AS DDIFF
GROUP BY DDIFF.inventory_id, DDIFF.customer_id) AS MXLST 

ON  DDIFF.TM = MXLST.WEEKS AND  MXLST.inventory_id = DDIFF.inventory_id AND MXLST.customer_id = DDIFF.customer_id

;

但是我得到了一些意想不到的结果(它应该返回多个行，最长持续时间为1.4286周，但由于某种原因我得到了1.0周)。我究竟做错了什么？我将非常感谢听到解释。

请注意，我已尝试遵循 this和 this正确使用 MAX() 但它仍然没有返回正确的结果。

编辑:可以找到 Sakila 表的结构 here

Answer 1

您的查询效率非常低:您在整个表上执行 3 个 SELECT，然后执行其他操作。我测试了您的查询:它已运行超过 60 秒而没有给出任何结果。只是杀死它并重写您的查询，如下所示:

SELECT c.first_name, c.last_name, f.title, DATEDIFF(r.return_date,r.rental_date)/7 AS rental_time 
FROM rental AS r
INNER JOIN inventory AS i 
ON r.inventory_id = i.inventory_id
INNER JOIN film AS f
ON i.film_id = f.film_id
INNER JOIN customer AS c
ON r.customer_id = c.customer_id
HAVING rental_time = (SELECT MAX(DATEDIFF(return_date,rental_date))/7 FROM rental)