SAKILA数据库中的电影类别与城市

Question

有人能帮我的SQL代码:

编写查询以查找每个城市中每个film_category的出现次数。按类别数递减的顺序排列。

我试过这样的东西，但我只能看到几个城市的计数。

SELECT 
name, city, COUNT(city) AS category_count
FROM
category
INNER JOIN
film_category USING (category_id)
INNER JOIN
film USING (film_id)
INNER JOIN
inventory USING (film_id)
INNER JOIN
store USING (store_id)
INNER JOIN
address USING (address_id)
INNER JOIN
city USING (city_id)
GROUP BY name , city
ORDER BY COUNT(name) DESC;

Answer 1

SELECT 
name, city, COUNT(city) AS category_count
FROM
category
JOIN film_category USING (category_id)
JOIN inventory USING (film_id)
JOIN store USING (store_id)
JOIN address USING (address_id)
JOIN city USING (city_id)
GROUP BY name, city
ORDER BY COUNT(name) DESC;

可以这样做的一种方法是删除电影类别上的连接，并直接连接film_category和category_id上的inventory。INNER保留字也是冗余的。

Answer 2

编写查询以查找每个城市中每个film_category的出现次数。按类别数递减的顺序排列。

SELECT name, city, COUNT(category_id) AS category_count
FROM category
INNER JOIN film_category
USING (category_id)
INNER JOIN inventory
USING(film_id)
INNER JOIN store
USING (store_id)
INNER JOIN address
USING (address_id)
INNER JOIN city
USING (city_id)
GROUP BY name, city
ORDER BY category_count DESC;