mysql - 将表插入新表时连接表时出现问题。

Question

现在我正在尝试将两个不同表中的信息连接到一个新表中。

这就是我现在拥有的。

insert into invoices_paid(InvoiceNumber,InvoiceDate,InvoiceTotal) 
select InvoiceNumber,InvoiceDate,InvoiceTotal
from Invoices
insert into invoices_paid(AccountNo, InvoiceLineItemAmount, InvoiceLineItemDescription) 
select AccountNo, InvoiceLineItemAmount, InvoiceLineItemDescription 
From InvoiceLineItems

图片有帮助。 Query

我需要红色框中的信息与左侧列中各自的信息相匹配。我对连接还很陌生，但我想我已经很接近了。有人可以帮忙吗？

Answer 1

连接 InvoiceNumber 上的 Invoices 和 InvoiceLineItems 表，并将其用作 INSERT 语句的 SELECT 子句的数据源。如:

insert into invoices_paid (InvoiceNumber,InvoiceDate,InvoiceTotal,
    AccountNo, InvoiceLineItemAmount, InvoiceLineItemDescription)
select
    inv.InvoiceNumber,InvoiceDate,InvoiceTotal,
    AccountNo, InvoiceLineItemAmount, InvoiceLineItemDescription
from 
    Invoices as inv
    inner join InvoiceLineItems as li on li.InvoiceNumber = inv.InvoiceNumber;