最近面试碰到一个数据库多对多查询，心有疑问。

Question

 

有如上一张多对多关系结构 ，电影表（主键，电影名字，上映日期，简介），演员表（主键，演员，出生地址，出生日期，简介）

一个电影有多个演员，一个演员演了多个电影。

 

增加电影

 

insert into movie(movie_id,name,sydate,note)values(movseq.nextval,'阿凡达',to_date('2010-04-12','yyyy-mm-dd'),'这是一部3D电影');
insert into movie(movie_id,name,sydate,note)values(movseq.nextval,'绝命快递',to_date('2010-09-02','yyyy-mm-dd'),'这是一部3D电影');
insert into movie(movie_id,name,sydate,note)values(movseq.nextval,'加勒比海盗',to_date('2011-03-09','yyyy-mm-dd'),'这是一部3D电影');
insert into movie(movie_id,name,sydate,note)values(movseq.nextval,'变形金刚',to_date('2011-05-15','yyyy-mm-dd'),'这是一部3D电影');
insert into movie(movie_id,name,sydate,note)values(movseq.nextval,'绿类侠',to_date('2011-10-22','yyyy-mm-dd'),'这是一部3D电影');

 增加演员

insert into performer(performer_id,name,address,birthdate,note)values(merseq.nextval,'张三','深圳',to_date('2000-04-02','yyyy-mm-dd'),'老实');
insert into performer(performer_id,name,address,birthdate,note)values(merseq.nextval,'李四','天津',to_date('2000-05-02','yyyy-mm-dd'),'严肃');
insert into performer(performer_id,name,address,birthdate,note)values(merseq.nextval,'王五','北京',to_date('2000-06-02','yyyy-mm-dd'),'未成年');
insert into performer(performer_id,name,address,birthdate,note)values(merseq.nextval,'赵六','南京',to_date('2000-07-02','yyyy-mm-dd'),'老头子');
insert into performer(performer_id,name,address,birthdate,note)values(merseq.nextval,'孙七','长沙',to_date('2000-08-02','yyyy-mm-dd'),'狡猾');
insert into performer(performer_id,name,address,birthdate,note)values(merseq.nextval,'钱八','上海',to_date('2000-09-02','yyyy-mm-dd'),'小偷');
insert into performer(performer_id,name,address,birthdate,note)values(merseq.nextval,'贺九','广东',to_date('2000-11-02','yyyy-mm-dd'),'老板');

 增加关系

insert into mov_per(movie_id,performer_id)values(2,2);
insert into mov_per(movie_id,performer_id)values(2,3);
insert into mov_per(movie_id,performer_id)values(2,4);
insert into mov_per(movie_id,performer_id)values(2,5);
insert into mov_per(movie_id,performer_id)values(2,6);
insert into mov_per(movie_id,performer_id)values(2,7);
insert into mov_per(movie_id,performer_id)values(3,2);
insert into mov_per(movie_id,performer_id)values(3,3);
insert into mov_per(movie_id,performer_id)values(4,4);
insert into mov_per(movie_id,performer_id)values(5,5);
insert into mov_per(movie_id,performer_id)values(6,6);
insert into mov_per(movie_id,performer_id)values(3,4);
insert into mov_per(movie_id,performer_id)values(3,7);
insert into mov_per(movie_id,performer_id)values(4,6);
insert into mov_per(movie_id,performer_id)values(4,2);
insert into mov_per(movie_id,performer_id)values(5,2);
insert into mov_per(movie_id,performer_id)values(6,2);
insert into mov_per(movie_id,performer_id)values(6,3);
insert into mov_per(movie_id,performer_id)values(5,7);
insert into mov_per(movie_id,performer_id)values(5,8);
insert into mov_per(movie_id,performer_id)values(3,8);
insert into mov_per(movie_id,performer_id)values(2,8);
insert into mov_per(movie_id,performer_id)values(4,8);
insert into mov_per(movie_id,performer_id)values(6,8);

 原问题是这样的：

     请写一个SQL语句查询演出电影数量大于3部并且上映的第一部电影是《阿凡达》的所有演员。

 

我想问的是，上映的第一部电影是《阿凡达》这个该怎么写，我自己想破脑袋结果也没想出来，= =|

如果能写出来全部SQL语句就更好了。

Answer 1

①最内层取得所有演出数量大于3的演员【红色】
②次外层就是在①的基础上取得这些演员的第一步电影【绿色】
③最外层就是在②的基础上看看哪些电影是阿凡达，然后筛选出演员【蓝色】

[color=blue]select per.*
from[/color] color=green
from mov_per mp, [/color]
color=red
from mov_per r, movie m, performer p
where r.movie_id = m.movie_id
and r.performer_id = p.performer_id
group by p.performer_id
having count(p.performer_id) > 3) t,[/color]
[color=green]movie mv
where mp.movie_id = mv.movie_id
and mp.performer_id = t.performer_id
group by t.performer_id) ttt, [/color]
p[color=blue]erformer per,
movie mov
where ttt.performer_id = per.performer_id
and ttt.movie_id = mov.movie_id
and mov.name = '阿凡达'[/color]