首页 > 试题广场 >

使用子查询的方式找出属于Action分类的所有电影对应的ti

[编程题]使用子查询的方式找出属于Action分类的所有电影对应的ti

热度指数：134184 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 256M，其他语言512M
算法知识视频讲解

film表

字段	说明
film_id	电影id
title	电影名称
description	电影描述信息

CREATE TABLE IF NOT EXISTS film (

film_id smallint(5) NOT NULL DEFAULT '0',

title varchar(255) NOT NULL,

description text,

PRIMARY KEY (film_id));

category表

字段	说明
category_id	电影分类id
name	电影分类名称
last_update	电影分类最后更新时间

CREATE TABLE category (

category_id tinyint(3) NOT NULL ,

name varchar(25) NOT NULL, `last_update` timestamp,

PRIMARY KEY ( category_id ));

film_category表

字段	说明
film_id	电影id
category_id	电影分类id
last_update	电影id和分类id对应关系的最后更新时间

CREATE TABLE film_category (

film_id smallint(5) NOT NULL,

category_id tinyint(3) NOT NULL, `last_update` timestamp);

你能使用子查询的方式找出属于Action分类的所有电影对应的title,description吗

输入描述:

无

输出描述:

无

示例1

输入

drop table if exists   film ;
drop table if exists  category  ; 
drop table if exists  film_category  ; 
CREATE TABLE IF NOT EXISTS film (
  film_id smallint(5)  NOT NULL DEFAULT '0',
  title varchar(255) NOT NULL,
  description text,
  PRIMARY KEY (film_id));
CREATE TABLE category  (
   category_id  tinyint(3)  NOT NULL ,
   name  varchar(25) NOT NULL, `last_update` timestamp,
  PRIMARY KEY ( category_id ));
CREATE TABLE film_category  (
   film_id  smallint(5)  NOT NULL,
   category_id  tinyint(3)  NOT NULL, `last_update` timestamp);
INSERT INTO film VALUES(1,'ACADEMY DINOSAUR','A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies');
INSERT INTO film VALUES(2,'ACE GOLDFINGER','A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China');
INSERT INTO film VALUES(3,'ADAPTATION HOLES','A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory');

INSERT INTO category VALUES(1,'Action','2006-02-14 20:46:27');
INSERT INTO category VALUES(2,'Animation','2006-02-14 20:46:27');
INSERT INTO category VALUES(3,'Children','2006-02-14 20:46:27');
INSERT INTO category VALUES(4,'Classics','2006-02-14 20:46:27');
INSERT INTO category VALUES(5,'Comedy','2006-02-14 20:46:27');
INSERT INTO category VALUES(6,'Documentary','2006-02-14 20:46:27');

INSERT INTO film_category VALUES(1,1,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(2,1,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(3,6,'2006-02-14 21:07:09');

输出

ACADEMY DINOSAUR|A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies
ACE GOLDFINGER|A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China

算法知识视频讲解

ciphersaw

根据题意，最简单粗暴的解法是直接 FROM 三张表查询，且用 WHERE 并列三个限定条件：

1、三个限定条件分别是【f.film_id = fc.film_id】、【fc.category_id = c.category_id 】、【c.name = 'Action'】

SELECT f.title, f.description 
FROM film f, film_category fc, category c
WHERE f.film_id = fc.film_id 
AND fc.category_id = c.category_id 
AND c.name = 'Action'

发表于 2017-07-18 15:48:49 回复(4)

更多回答

Mysql

牛客778439756号

select

f.title,f.description

from film f

join film_category fc

on fc.film_id=f.film_id

join category c

on c.category_id=fc.category_id

where c.name='Action'

发表于 2022-07-18 16:28:33 回复(0)

Mysql

买飞飞也用券

select
  title,
  description
from
  film
where
  film_id in (
    select
      film_id
    from
      category
      join film_category using(category_id)
    where
      name = 'Action'
  )

把下面两个表链接起来，筛选类名为"Action"的film_id 之后作为筛选条件

发表于 2022-06-02 13:52:12 回复(0)

Sqlite

Stzolo

select title, description
from film
where film_id in (
select film_id
from film_category where category_id in (
select category_id
from category
where name = 'Action'
)
)

发表于 2022-04-15 19:11:53 回复(0)

Mysql

一周后告诉你面试结果

select title,description
from film 
where (title,description) in 
(select f.title,f.description
from film f
left join film_category fc on f.film_id=fc.film_id
join category c on c.category_id=fc.category_id
where c.name='Action')

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

发表于 2022-03-13 11:09:01 回复(0)

Sqlite

小客驾到

select f.title,f.description from film f,(
select fc.film_id from category c,film_category fc where fc.category_id = c.category_id
and c.name = 'Action') a
where a.film_id = f.film_id
group by f.title

发表于 2021-10-27 15:15:06 回复(0)

Mysql

牛客342783178号

select title,description
from film
where film_id in
      (select film_id 
       from film_category 
       where category_id in
             (select category_id from category where name="Action"))

发表于 2021-09-03 22:06:33 回复(0)

Mysql

xiaohuanxiong

子查询：

SELECT film.title,film.description
FROM
    film,
    film_category
where film.film_id = film_category.film_id
and film_category.category_id = (
    select category_id
    from category
    where name = 'Action'
);

常规解法：

SELECT film.title,film.description
FROM
    film,
    film_category,
    category
where film.film_id = film_category.film_id
and film_category.category_id = category.category_id
and category.name = 'Action';

发表于 2021-08-15 00:41:20 回复(0)

WillGO

select title , description From film
where film.film_id in(
select film_category.film_id from category ,film_category
where category.category_id = film_category.category_id
and category.name = 'Action')

发表于 2021-07-27 20:51:50 回复(0)

小胖球

select title,description from film
where film_id in(

select film_id from film_category
where category_id =(
select category_id from category
where name="Action"
));

发表于 2021-07-06 12:04:02 回复(0)

有滋有味小熊鞋

select f.title,f.description
from film f inner join film_category fc
on f.film_id = fc.film_id
where fc.category_id in (select category_id
from category
where name = 'Action');

发表于 2021-06-04 16:12:53 回复(1)

vc89757

select title, description
from film left join film_category as fc
on film.film_id=fc.film_id
join category as c
on c.category_id=fc.category_id
where c.name="Action";

发表于 2021-05-19 17:24:19 回复(0)

一次不死并且超神

select f.title, f.description
from film f left join film_category fc
on f.film_id = fc.film_id
where fc.category_id
in (select category_id from category where name = 'Action')

发表于 2021-04-08 16:55:46 回复(0)

＜（＾－＾）＞

/*
使用子查询的方式找出属于Action分类的所有电影对应的title,description
1.查出Action分类
select f.film_id from film f, category c, film_category ca
where f.film_id = ca.film_id
and ca.category_id = c.category_id
and c.name = 'Action'
2.查出属于Action分类的所有电影对应的title,description
ps: 这里is写错为=，哈哈头都大了
*/
select f.title, f.description from film f
where f.film_id in (
select f.film_id from film f, category c, film_category ca
where f.film_id = ca.film_id
and ca.category_id = c.category_id
and c.name = 'Action'
)

发表于 2021-03-28 19:50:06 回复(0)

simple_sunshine

select fi.title,fi.description
from film as fi join film_category as fc
    on fi.film_id = fc.film_id
        join category as ca
         on ca.category_id = fc.category_id 
            and ca.name = 'Action';

发表于 2021-03-08 09:02:00 回复(0)

qstalking

【整体思路】运用子查询。分类名称找分类ID，分类ID找电影ID，电影ID找电影名称和电影描述。

【写码步骤】上述思路倒过来，从film表中取电影名称和电影描述（用分类ID限定电影ID（用分类名称限定分类ID））

【具体代码】

select f1.title

,f1.description
from film f1
where f1.film_id in
(select f2.film_id from film_category f2
where f2.category_id in
(select category_id from category
where name='Action'));

发表于 2021-01-24 00:23:14 回复(0)

suo_li

select f.title,
       f.description
from film f
left join (select fc.film_id
     from film_category fc
     left join category c on fc.category_id=c.category_id
     where c.name='Action') t1 on f.film_id=t1.film_id

请问大家上述代码错在哪里？实际输出比期望输出多一行数据，也没未能看到这样解法的正确答案。

发表于 2020-12-10 20:30:13 回复(0)

小夕闭关ing

select title, description from film
where film_id in (
select film_id from film_category
where category_id in 
(select category_id from category
where name like 'action'))

以上是方法一，多层内嵌子查询

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'

以上是方法二，直接用子查询加where限制表的关系

发表于 2020-08-11 10:52:22 回复(0)

周中山

-- action分类的分类id下的所有电影 2 找到电影名称
select m.title,m.description from film m where m.film_id in (
select fy.film_id from film_category fy inner join category y on fy.category_id = y.category_id
where y.name = 'Action' )

发表于 2020-08-09 14:00:22 回复(0)

天天256

本题需要注意的点：

1.因为子查询查出来的film_id结果不止一个，所以需要使用关键字in

2.需要注意子查询中两表连接需要用join连接，使用并列查询不通过

3.需要注意字符串是区分大小写，书写时还需用引号并且首字母大写。

select f.title,f.description from film as f

where f.film_id in (select fc.film_id from film_category as fc join category as ca
on fc.category_id=ca.category_id and ca.name='Action');

发表于 2019-12-13 11:23:52 回复(0)

顺利毕业和拿到大厂offer

SELECT title,description
FROM film
where film_id in
(select fc.film_id
from film_category as fc
inner join category as c
on fc.category_id = c.category_id

and c.name = 'Action')

SQL 嵌套查询，符合题目要求的子查询

发表于 2019-09-04 13:20:51 回复(0)