表结构
CREATE TABLE Movie
( title CHAR(20) NOT NULL, --电影名称
year INT NOT NULL, --拍摄年份
length INT , --长度
inColor BIT DEFAULT 1, --是否彩色
studioName CHAR(20), --所属电影公司
producerCertID CHAR(10), --制片人
PRIMARY KEY (title,year),
);
CREATE TABLE StarsIn
( movieTitle CHAR(20), --电影名称
movieYear INT , --拍摄年份
starName CHAR(20), --演员姓名
PRIMARY KEY(movieTitle,movieYear,starName),
FOREIGN KEY(movieTitle, movieYear) REFERENCES Movie(title,year)
);
表样例
Movie表:
title | year | length | inColor | studioName | producerCertID |
---|---|---|---|---|---|
M1 | 2018 | 100 | 1 | st1 | 0001 |
M2 | 2018 | 100 | 1 | st1 | 0002 |
M3 | 2019 | 100 | 1 | st2 | 0003 |
M4 | 2017 | 100 | 1 | st2 | 0004 |
M5 | 2014 | 100 | 1 | st2 | 0004 |
M6 | 2015 | 100 | 1 | st2 | 0004 |
M7 | 2018 | 95 | 1 | st2 | 0003 |
StarsIn表:
movieTitle | movieYear | starName |
---|---|---|
M1 | 2018 | S1 |
M2 | 2018 | S1 |
M3 | 2019 | S2 |
M4 | 2017 | S3 |
M3 | 2019 | S3 |
M3 | 2019 | S4 |
M7 | 2018 | S2 |
输出样例:
starName |
---|
S1 |
SELECT distinct starName #distinct:确保数据大量时输出不同的结果
FROM StarsIn
WHERE movieYear=2018 AND movieTitle IN (SELECT title
FROM Movie
WHERE studioName='st1' and year=2018);
第二个条件里的year限制也很重要,本人认为是由于Movie表里是title和year两个属性联合做主码,而StarsIn表里参照的外码是Movie里的主码因此 限制条件要特别声明年份