题解 | #使用子查询的方式找出属于Action分类的所有电影对应的title,description#

1.先写个简单方便直来直去不用子查询的开开胃。

select f.title, f.description 
from film as f, category as c, film_category as fc
where f.film_id = fc.film_id and fc.category_id = c.category_id and
      c.name = "Action"

2.连接film和film_category表，用子查询从category表中选出name=“Action”的category_id，然后用主查询的category_id跟子查询结果匹配，最后选出title和description。

select f.title, f.description 
from film as f, film_category as fc
where f.film_id = fc.film_id 
and fc.category_id = (select category_id from category 
                      where name = "Action")

3.这次是用子查询连接category表和film_category表，限制category表的name=“Action”，并选出film_id，这时子查询的结果就是所有Action类的film_id。然后从film表选出title和description并限制film_id在子查询的结果中。

select f.title, f.description 
from film as f
where f.film_id in (select fc.film_id 
                   from category as c, film_category as fc
                   where c.category_id = fc.category_id and 
                         c.name = "Action")