MySQL在FROM关联了子查询

Question

我正在使用Sakila样本数据库，并试图获得每个国家观看次数最多的电影。到目前为止，我已经通过以下查询获得了某个国家的id观看次数最多的电影：

SELECT 
    F.title, CO.country, count(F.film_id) as times
FROM 
    customer C 
INNER JOIN 
    address A ON C.address_id = A.address_id
INNER JOIN 
    city CI ON A.city_id = CI.city_id
INNER JOIN 
    country CO ON CI.country_id = CO.country_id
INNER JOIN 
    rental R ON C.customer_id = R.customer_id
INNER JOIN 
    inventory I ON R.inventory_id = I.inventory_id
INNER JOIN 
    film F ON I.film_id = F.film_id
WHERE 
    CO.country_id = 1
GROUP BY 
    F.film_id
ORDER BY 
    times DESC
LIMIT 1;

我怀疑我将不得不在另一个查询的FORM中使用这个查询或类似的东西，但我已经尝试了我所能想到的一切，完全不知道如何做到这一点。

提前感谢！

Answer 1

我承认，这是一个地狱般的查询。但好吧，只要它有效。

说明：

• 子查询：几乎与您已经拥有的相同。没有CCD_ 1和CCD_。生成每个国家的电影计数列表
• 结果，按国家分组
• GROUP_CONCAT(title ORDER BY times DESC SEPARATOR '|||')，将给出该"行"中的所有标题，首先是查看次数最多的标题。分隔符并不重要，只要你确信它永远不会出现在标题中
• SUBSTRING_INDEX('...', '|||', 1)会产生字符串的第一部分，直到它找到|||，在这种情况下是第一个（因此也是查看次数最多的）标题

完整查询：

SELECT
    country_name,
    SUBSTRING_INDEX(
        GROUP_CONCAT(title ORDER BY times DESC SEPARATOR '|||'), 
        '|||', 1
    ) as title,
    MAX(times)
FROM (
    SELECT 
        F.title AS title, 
        CO.country_id AS country_id,
        CO.country AS country_name, 
        count(F.film_id) as times
    FROM customer C INNER JOIN address A ON C.address_id = A.address_id
    INNER JOIN city CI ON A.city_id = CI.city_id
    INNER JOIN country CO ON CI.country_id = CO.country_id
    INNER JOIN rental R ON C.customer_id = R.customer_id
    INNER JOIN inventory I ON R.inventory_id = I.inventory_id
    INNER JOIN film F ON I.film_id = F.film_id
    GROUP BY F.film_id, CO.country_id
) AS count_per_movie_per_country
GROUP BY country_id

概念证明（只要子查询正确）：SQLFiddle