SQL筛选妮可·基德曼合作演员：category字段报错解决方案咨询

需求说明 #

使用SQLite列出所有与1967年出生的妮可·基德曼合作过的不同actor和actress，仅输出姓名，按字母顺序排列，需包含妮可本人，姓名不重复。

表结构 #

crew表 #

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);

people表 #

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

titles表 #

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 (title_id);
CREATE INDEX ix_titles_primary_title ON titles (primary_title);
CREATE INDEX ix_titles_original_title ON titles (original_title);

原问题 #

执行以下SQL时出现报错：in prepare, no such column: category (1)

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;

错误原因 #

category字段属于crew表，而原SQL的HAVING子句是基于people表的分组结果进行筛选，无法直接访问crew表的字段，因此触发字段不存在的报错。

修正后的SQL #

SELECT DISTINCT p.name
FROM people p
JOIN crew c ON p.person_id = c.person_id
WHERE c.title_id IN (
    SELECT title_id 
    FROM crew 
    WHERE person_id = (SELECT person_id FROM people WHERE name = 'Nicole Kidman' AND born = 1967)
)
AND c.category IN ('actor', 'actress')
ORDER BY p.name ASC;

说明 #

1. 首先通过子查询定位1967年出生的妮可·基德曼的person_id，并获取她参与的所有作品title_id
2. 将people表与crew表关联，筛选出这些作品中category为actor或actress的人员
3. 使用DISTINCT确保姓名不重复，最后按姓名字母顺序排序

内容的提问来源于stack exchange，提问作者Hiiiii