MySQL：重命名派生表列以实现派生表间的INSERT操作

嘿，我来帮你解决这个SQL问题！首先得纠正一个你原写法里的关键错误：INSERT INTO后面不能直接跟一个派生表（就是你写的SELECT ... AS DerivedTable1），因为派生表只是临时的查询结果，不是实际存在的数据库表，根本没法往里面插入数据哦。

那正确的做法分两种情况，看你具体需求来选：

情况1：需要把数据存入实际表（适合长期存储合并后的数据） #

首先，你可以先基于第一个查询的结构创建一个实际表（比如叫sale_combined）：

CREATE TABLE sale_combined AS
SELECT 
    saleDetails.ActualPostedDate, 
    saleDetails.MasterCust, 
    saleDetails.ProdName, 
    saleDetails.QtySold, 
    distributorPricing.item_name, 
    distributorPricing.package_name, 
    distributorPricing.fob_price, 
    distributorPricing.volume_gal, 
    distributorPricing.volume_ce, 
    customerDetails.Chain, 
    customerDetails.Premise, 
    customerDetails.Channel, 
    customerDetails.Territory 
FROM saleDetails 
LEFT JOIN distributorPricing USING (ProdName) 
LEFT JOIN customerDetails USING (MasterCust);

这个表的列结构和你原来的DerivedTable1完全一致。

接下来，把第二个查询里的StartDate重命名为ActualPostedDate，然后插入到这个新表里就行：

INSERT INTO sale_combined
SELECT 
    saleDetails_other.StartDate AS ActualPostedDate, -- 核心操作：给列重命名，匹配目标表的列名
    saleDetails_other.MasterCust, 
    saleDetails_other.ProdName, 
    saleDetails_other.QtySold, 
    distributorPricing_other.item_name, 
    distributorPricing_other.package_name, 
    distributorPricing_other.fob_price, 
    distributorPricing_other.volume_gal, 
    distributorPricing_other.volume_ce, 
    customerDetails_other.Chain, 
    customerDetails_other.Premise, 
    customerDetails_other.Channel, 
    customerDetails_other.Territory 
FROM saleDetails_other 
LEFT JOIN distributorPricing_other USING (ProdName) 
LEFT JOIN customerDetails_other USING (MasterCust);

这样列名完全匹配，INSERT操作就能顺利执行了。

情况2：只需要一次性获取合并后的结果（不用存储） #

如果只是想直接得到两个数据集合并后的查询结果，不用存入表，可以直接用UNION ALL：

SELECT 
    saleDetails.ActualPostedDate, 
    saleDetails.MasterCust, 
    saleDetails.ProdName, 
    saleDetails.QtySold, 
    distributorPricing.item_name, 
    distributorPricing.package_name, 
    distributorPricing.fob_price, 
    distributorPricing.volume_gal, 
    distributorPricing.volume_ce, 
    customerDetails.Chain, 
    customerDetails.Premise, 
    customerDetails.Channel, 
    customerDetails.Territory 
FROM saleDetails 
LEFT JOIN distributorPricing USING (ProdName) 
LEFT JOIN customerDetails USING (MasterCust)
UNION ALL
SELECT 
    saleDetails_other.StartDate AS ActualPostedDate, 
    saleDetails_other.MasterCust, 
    saleDetails_other.ProdName, 
    saleDetails_other.QtySold, 
    distributorPricing_other.item_name, 
    distributorPricing_other.package_name, 
    distributorPricing_other.fob_price, 
    distributorPricing_other.volume_gal, 
    distributorPricing_other.volume_ce, 
    customerDetails_other.Chain, 
    customerDetails_other.Premise, 
    customerDetails_other.Channel, 
    customerDetails_other.Territory 
FROM saleDetails_other 
LEFT JOIN distributorPricing_other USING (ProdName) 
LEFT JOIN customerDetails_other USING (MasterCust)
ORDER BY ActualPostedDate;

这个查询会把两个结果集合并成一个，第一个查询的ActualPostedDate和第二个查询重命名后的列会被合并到同一列里。要是你需要自动去重，可以把UNION ALL换成UNION，不过UNION ALL的执行效率更高，优先推荐用它。

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