mysql - 将表插入新表时无法连接表。

现在，我正在尝试将来自两个不同表的信息合并到一个新表中。

这就是我现在所拥有的。

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

我需要红色框中的信息与左侧栏中的相应信息相匹配。我对join相当陌生，但我认为我已经很接近了。有人可以帮忙吗？

最佳答案

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