mysql - SQL查找每个类别中长度大于其各自类别平均长度的下5部最长的电影？

Question

来自可用的 sakila 数据库 here ，我想在每个类别中找到接下来 5 部最长的电影。我做了这个问题。使用查询

SELECT fl.title, fl.length, ct.name
FROM film fl JOIN film_category fc ON fl.film_id=fc.film_id
             JOIN category ct ON ct.category_id=fc.category_id
WHERE fl.length > (SELECT AVG(fl.length) 
                   FROM film fl JOIN film_category fc ON fl.film_id = fc.film_id
                   JOIN category cat ON cat.category_id=fc.category_id
                   WHERE cat.name=ct.name);

我可以找到所有长度大于各自类别中电影平均长度的电影。当我添加 LIMIT 5 时，只检索到 5 行 - 我需要从每个类别中获取 5 行。添加 GROUP BY ct.name 仅给出 16 行 - 每个类别各一行。

1. 什么查询可以完成这项工作？
2. 是否可以在不使用任何相关子查询的情况下实现这一点？
3. 什么可以帮助我确定关联子查询的结果是否通过连接表来实现？

我认为它类似于this ，但我想不出解决办法。

谢谢!

Answer 1

以下已经针对 Sakila 数据库进行了(成功)测试......

SELECT title,
       length,
       categoryName
FROM ( SELECT CASE
                  WHEN film_category.category_id = @currentCategory THEN
                      @currentRecord := @currentRecord + 1
                  ELSE
                      @currentRecord := 1 AND
                      @currentCategory := film_category.category_id
              END AS recordNumber,
              film.title AS title,
              film.length AS length,
              categoryName AS categoryName
       FROM film
       JOIN film_category ON film.film_id = film_category.film_id
       JOIN ( SELECT film_category.category_id AS category_id,
                     category.name AS categoryName,
                     AVG( film.length ) AS categoryAvgLength
              FROM film
              JOIN film_category ON film.film_id = film_category.film_id
              JOIN category ON category.category_id = film_category.category_id
              GROUP BY film_category.category_id,
                       category.name
            ) AS categoryAvgLengthFinder ON film_category.category_id = categoryAvgLengthFinder.category_id,
            (
                SELECT @currentCategory := 0 AS currentCategory
            ) AS currentValuesInitialiser
       WHERE film.length > categoryAvgLength
       ORDER BY film_category.category_id,
                film.length
     ) AS allLarger
WHERE recordNumber <= 5;

该语句首先使用以下子查询形成一个由每个 category 组成的“表”的唯一标识符 category_id , name的 category和相应的平均值 Film length ...

SELECT film_category.category_id AS category_id,
       category.name AS categoryName,
       AVG( film.length ) AS categoryAvgLength
FROM film
JOIN film_category ON film.film_id = film_category.film_id
JOIN category ON category.category_id = film_category.category_id
GROUP BY film_category.category_id,
       category.name

此子查询的结果然后连接到 film和 film_catgory .当子查询从 category 检索所有详细信息时我们将需要声明的其余部分，否 JOIN与 category是需要的。

然后将生成的数据集与 SELECT @currentCategory := 0 AS currentCategory 交叉连接初始化变量 @currentCategory在同一声明中。这确实是以附加一个名为 currentCategory 的字段为代价的。到上面生成的数据集，所以你可能更喜欢使用下面的代码......

SET @currentCategory := 0;
SELECT title,
       length,
       categoryName
FROM ( SELECT CASE
                  WHEN film_category.category_id = @currentCategory THEN
                      @currentRecord := @currentRecord + 1
                  ELSE
                      @currentRecord := 1 AND
                      @currentCategory := film_category.category_id
              END AS recordNumber,
              film.title AS title,
              film.length AS length,
              categoryName AS categoryName
       FROM film
       JOIN film_category ON film.film_id = film_category.film_id
       JOIN ( SELECT film_category.category_id AS category_id,
                     category.name AS categoryName,
                     AVG( film.length ) AS categoryAvgLength
              FROM film
              JOIN film_category ON film.film_id = film_category.film_id
              JOIN category ON category.category_id = film_category.category_id
              GROUP BY film_category.category_id,
                       category.name
            ) AS categoryAvgLengthFinder ON film_category.category_id = categoryAvgLengthFinder.category_id
       WHERE film.length > categoryAvgLength
       ORDER BY film_category.category_id,
                film.length
     ) AS allLarger
WHERE recordNumber <= 5;

一旦JOIN的执行(并且 @currentCategory 被初始化)，结果数据集被细化为那些值为 film.length' is greater than the corresponding average for that category. The refined dataset is then sorted (not grouped) by one of the 的记录category_id fields (of which there will be two sharing the same value owing to the joining) and subsorted by电影长度`.

选择字段后，每条记录的值为 category_id与 @currentCategory 的值进行比较.如果它们不匹配，则 @currentRecord初始化为 1和 @currentCategory更新为新值 category_id .如果它们匹配，则 @currentRecord递增。在任何一种情况下，分配给 @currentRecord 的值返回到 SELECT在给定别名 recordNumber 的字段中声明.因此，我们能够为我们改进的数据集添加一个记录编号。

那么剩下的就是SELECT细化数据集(无记录号)中记录号小于或等于 5 的所有记录.

请注意，上面的结果没有使用任何相关的子查询，而是使用了 JOIN到表和子查询。

如果您有任何问题或意见，请随时发表相应的评论。