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

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

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

增加电影

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语句就更好了。

①最内层取得所有演出数量大于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]

movie可以查出在阿凡达之前的电影，也就是没有出演过这些电影

上映的第一部电影是《阿凡达》这个应该用

select mp.performer_id from mov_per mp join movie m on mp.movie_id=m.movie_id where group by performer_id having min(m.sydate) = (select sydate from movie m1 where m1.name="阿凡达");

就是说 选出演员演过的电影阿凡达的并且是最早放映的

完整的就是
[code="java"]select mp.performer_id, p.name from mov_per mp join movie m on mp.movie_id=m.movie_id join performer p on mp.performer_id= p.performer_id group by p.performer_id having min(m.sydate) = (select sydate from movie m1 where m1.name="阿凡达") and count(mp.movie_id) > 3 ;[/code]

没试过能不能通过。。。反正类似这样

取得演出电影数量大于3部并且上映的第一部电影是《阿凡达》的所有演员的语句

select per.*
from (select t.performer_id, mv.movie_id, min(mv.sydate)
from mov_per mp,
(select p.performer_id, count(p.performer_id)
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,
movie mv
where mp.movie_id = mv.movie_id
and mp.performer_id = t.performer_id
group by t.performer_id) ttt,
performer per,
movie mov
where ttt.performer_id = per.performer_id
and ttt.movie_id = mov.movie_id
and mov.name = '阿凡达'

[code="sql"]
SELECT DISTINCT p.name
(
SELECT p.performer_id,p.name,min(m.sydate) AS min_sydate
FROM performer AS p
JOIN mov_per AS mp ON p.performer_id=mp.performer_id
JOIN movie AS m ON mp.movie_id=m.movie_id
GROUP BY p.performer_id,p.name
HAVING COUNT(*)>3
) AS p
JOIN mov_per AS mp ON p.performer_id=mp.performer_id
JOIN movie AS m ON mp.movie_id=m.movie_id AND m.sydate=p.min_sydate
WHERE m.name='阿凡达'
[/code]

1.第一个关联表，查询“演出数量大于3的演员”，同时把其参演的第一部电影的上映日期查出。
2.外层再关联电影表，判断第一部电影是阿凡达的，考虑到可能存在有1部以上的电影同一天上映，所以使用“SELECT DISTINCT p.name”