錯誤程式碼 1054：向表中插入數據時出現未知列

我一直在嘗試將具有不同列的兩個單獨表中的數據連接到第三個表中，但是

錯誤程式碼 1054：欄位列表中的未知列“公司”

不斷彈出。

我目前的程式碼是：

INSERT INTO Company (Year, Company, Revenue, Profits) 
(select Year, Company, Revenue, Profits from 
(select Year, Comp_Name from Comp_Info
union
select Revenue, Profits from Comp_Financials) 
AS tempTable);

Comp_Financials 表：

Comp_Info 表：

兩個表將合併到已創建的表中：Company (ID int auto_increment, Year int, Company varchar (20), Revenue int, Profits int, Primary key (ID))

任何幫助是極大的讚賞！

您需要使用JOINbyID而不是UNION.

使用JOIN不需要子查詢。

在(SELECT 之後也不需要在 SELECT 之前INSERT INTO。

所以工作查詢是：

INSERT INTO Company (Year, Company, Revenue, Profits) 
SELECT CI.Year, CI.Comp_Name AS Company, CF.Revenue, CF.Profits
FROM Comp_Info AS CI
JOIN Comp_Financials AS CF ON CF.ID = CI.ID;

---

更多解釋：

您嘗試過UNION的方法是錯誤的。讓我用一個小例子來解釋。

我正在創建Comp_Info並且Comp_Financials是範例的臨時表

CREATE TEMPORARY TABLE Comp_Info (Id INT, Year INT, Comp_Name VARCHAR (200));

INSERT INTO Comp_Info (Id, Year, Comp_Name) VALUES
(1, 2005, 'Wal-Mart'), (2, 2005, 'Exxon Mobil'), (3, 2005, 'General Motors');

CREATE TEMPORARY TABLE Comp_Financials (Id INT, Revenue INT, Profits INT);

INSERT INTO Comp_Financials (Id, Revenue, Profits) VALUES
(1, 288189, 10267), (2, 270772, 25330), (3, 193517, 2805);

使用樣本數據，UNION結果如下：

SELECT Year, Comp_Name FROM Comp_Info
UNION
SELECT Revenue, Profits FROM Comp_Financials;

|Year      | Comp_Name       |
|----------|---------------- |
|2005      | Wal-Mart        |
|2005      | Exxon Mobil     |
|2005      | General Motors  |
|288189    | 10267           |
|270772    | 25330           |
|193517    | 2805            |

但使用相同的樣本數據，JOIN結果如下：

SELECT CI.Year, CI.Comp_Name AS Company, CF.Revenue, CF.Profits
FROM Comp_Info AS CI
JOIN Comp_Financials AS CF ON CF.ID = CI.ID;

| Year  | Company           | Revenue   | Profits   
|------ |------------------ |---------- |---------
| 2005  | Wal-Mart          | 288189    | 10267
| 2005  | Exxon Mobil       | 270772    | 25330
| 2005  | General Motors    | 193517    | 2805

引用自：https://dba.stackexchange.com/questions/144042