sqlite 如何在SQL中正确实现多个需求

4si2a6ki  于 2023-01-09  发布在  SQLite
关注(0)|答案(1)|浏览(181)

使用sqlite列出所有不同的演员和曾与妮可·基德曼(生于1967年)合作过的女演员。详细信息:只按字母顺序打印男演员和女演员的名字。答案应该包括妮可·基德曼本人。每个名字在输出中只应该出现一次。注意:正如在模式中提到的,当考虑团队中个人的角色时,请参考字段类别。角色"男演员"和"女演员"是不同的,应该这样考虑。
我有三节课
1.船员:

CREATE TABLE crew (
  title_id VARCHAR, -- REFERENCES titles (title_id),
  person_id VARCHAR, -- REFERENCES people (person_id),
  category VARCHAR,
  job VARCHAR,
  characters VARCHAR
);
CREATE INDEX ix_crew_title_id ON crew (title_id);
CREATE INDEX ix_crew_person_id ON crew (person_id);

1.人:

CREATE TABLE people (
  person_id VARCHAR PRIMARY KEY,
  name VARCHAR,
  born INTEGER,
  died INTEGER
);
CREATE INDEX ix_people_name ON people (name);

1.标题:

CREATE TABLE titles (
  title_id VARCHAR PRIMARY KEY,
  type VARCHAR,
  primary_title VARCHAR,
  original_title VARCHAR,
  is_adult INTEGER,
  premiered INTEGER,
  ended INTEGER,
  runtime_minutes INTEGER,
  genres VARCHAR
);
CREATE INDEX ix_titles_type ON titles (type);
CREATE INDEX ix_titles_primary_title ON titles (primary_title);
CREATE INDEX ix_titles_original_title ON titles (original_title);

我的解决方案是:

SELECT DISTINCT name FROM people
GROUP BY
    person_id
HAVING person_id IN (SELECT person_id FROM crew WHERE title_id IN (SELECT title_id FROM crew WHERE person_id IN (SELECT person_id FROM people WHERE name='Nicole Kidman')))
AND category IN ('actor', 'actress')
ORDER BY name ASC;

但输出显示错误:第一个月
如果我删除此行:AND category IN ('actor', 'actress')它工作
所以我想知道我怎样才能添加一个过滤器来检查类别应该是'演员'或'女演员'希望有人能给我一个提示

xjreopfe

xjreopfe1#

SELECT DISTINCT name FROM people
GROUP BY
    person_id
HAVING person_id IN (SELECT person_id FROM crew WHERE title_id IN (SELECT title_id FROM crew WHERE person_id IN (SELECT person_id FROM people WHERE name='Nicole Kidman')) AND category IN ('actor', 'actress'))
ORDER BY name ASC;

这是因为我把person_id IN (SELECT person_id FROM people WHERE name='Nicole Kidman'))category IN ('actor', 'actress')放到了同一个WHERE' clause. Otherwise, it can not find范畴in the table because it does not included in WHERE '

相关问题