我有一个电影的Postgres数据库,我想高效地列出所有由演员扮演的电影,并列出电影中的所有演员。
目前我正在使用这两个SELECT语句(我认为有更好的方法来做到这一点):
SELECT title FROM movies,castings WHERE movies.id = castings.movieid and castings.actorid = (SELECT id FROM actors where name = $name and sname = $sname);
SELECT name, sname FROM actors,castings WHERE actors.id = castings.actorid and castings.movieid = (SELECT id FROM movies where title = $title);
数据库:
CREATE SEQUENCE directors_id_seq;
CREATE TABLE directors (
id BIGINT PRIMARY KEY DEFAULT nextval('directors_id_seq'),
name TEXT,
sname TEXT
);
CREATE SEQUENCE actors_id_seq;
CREATE TABLE actors (
id BIGINT PRIMARY KEY DEFAULT nextval('actors_id_seq'),
name TEXT,
sname TEXT
);
CREATE SEQUENCE movies_id_seq;
CREATE TABLE movies (
id BIGINT PRIMARY KEY DEFAULT nextval('movies_id_seq'),
title TEXT NOT NULL,
year INT NOT NULL,
votes INT,
score INT,
director BIGINT NOT NULL REFERENCES directors (id)
);
CREATE TABLE castings (
movieid BIGINT REFERENCES movies (id),
actorid BIGINT REFERENCES actors (id),
ord INT,
PRIMARY KEY (movieid, actorid)
);
我该如何列出一个演员所演过的所有电影? 我该如何列出一部电影中所有的演员?
-- 谢谢,