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

0 0

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

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

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

增加电影

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

面试秘籍

2011年11月18日 15:01

ynwyyfghy
20
0 0 0

查看图片附件

采纳的答案

①最内层取得所有演出数量大于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 = '阿凡达'

2011年11月18日 15:50

kendylxy
328
0 0 0

添加评论

0 0

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='阿凡达'

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

2011年11月19日 11:28

xinannansha
158
0 0 1

添加评论

0 0

取得演出电影数量大于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 = '阿凡达'

2011年11月18日 15:44

kendylxy
328
0 0 0

添加评论

0 0

完整的就是

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 ;

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

2011年11月18日 15:40

asyty
1942
0 1 29

添加评论

0 0

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

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="阿凡达");

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

2011年11月18日 15:36

asyty
1942
0 1 29