mysql - 'e.eid' 中的未知列 'field list'

Question

使用 MySQL 并收到此错误，以下是我的 Works_on 表和插入中的代码片段

 DROP TABLE IF EXISTS `works_on`;
CREATE TABLE works_on(
eid INT NOT NULL,
pid INT NOT NULL,
start_date DATE,
PRIMARY KEY (eid, pid),
FOREIGN KEY (eid) REFERENCES employee(id),
FOREIGN KEY (pid) REFERENCES project(id)
);

INSERT INTO works_on
(eid, pid, start_date)
SELECT
e.eid AS eid, p.pid AS eid, '2012-01-01'
FROM project p, employee e
WHERE e.first_name = 'Adam' AND e.last_name = 'Lowd'
AND p.name = 'Diamond';

Answer 1

我猜你打算:

INSERT INTO works_on (eid, pid, start_date)
    SELECT e.id AS eid, p.id AS pid, '2012-01-01'
    FROM project p CROSS JOIN
         employee e
    WHERE e.first_name = 'Adam' AND e.last_name = 'Lowd' AND p.name = 'Diamond';