我收到一个错误，说输出与给定的问题不匹配
Sakila数据库:https://dev.mysql.com/doc/Sakila/en/sakila-structure.html
下面是我的代码
SELECT c.name AS 'City'
, SUM(p.amount) AS 'Gross'
FROM category c
JOIN film_category fc
ON c.category_id = fc.category_id
JOIN inventory i
ON fc.film_id = i.film_id
JOIN rental r
ON i.inventory_id = r.inventory_id
JOIN payment p
ON r.rental_id = p.rental_id
GROUP
BY c.name
ORDER
BY Gross
LIMIT 1;
预期结果是查找为组织产生最大收入的城市。
请指正我哪里做得不对，还是有其他做法。
试试这个
SELECT City, Gross
FROM (SELECT c.name AS City, SUM(p.amount) AS Gross
FROM category c
JOIN film_category fc
ON (c.category_id=fc.category_id)
JOIN inventory i
ON (fc.film_id=i.film_id)
JOIN rental r
ON (i.inventory_id=r.inventory_id)
JOIN payment p
ON (r.rental_id=p.rental_id)
GROUP BY c.name
) A
ORDER BY GROSS DESC LIMIT 1;
请尝试以下操作:
从city_id=的城市中选择city（从address中选择city_id作为加入客户作为c ON a.Address_ID=c.Address_ID加入付款作为p ON c.Customer_ID=p.Customer_ID按a.city_id按sum（amount）DESC LIMIT 1排序Customer_ID组）；
