合并 SQL 查询：获取采购订单信息和对应过账日期

本文介绍如何将两个 SQL 查询语句合并，以获取采购订单信息和其对应的过账日期。第一个 SQL 用于获取采购订单的基本信息，第二个 SQL 用于获取过账日期，最终将两个 SQL 查询结果合并到一个查询结果中。

第一个 SQL：

SELECT 
    opoh.PurchaseOrder AS purchaseorder, 
    opoh.PurchaseOrderType AS purchaseordertype, 
    opoh.Supplier AS supplier, 
    opoh.PurchasingOrganization AS purchasingorganization, 
    opoh.PurchasingGroup AS purchasinggroup, 
    opoh.CompanyCode AS companycode, 
    opoh.PaymentTerms AS paymentterms, 
    opoh.ZZ1_PosttingDate_PDH AS create_time, 
    opoh.ZZ1_PosttingDate_PDH AS zZ1PosttingDatePDH, 
    opoh.ZZ1_PosttingDate_PDH AS posttingdate, 
    opoh.DocumentCurrency AS documentCurrency, 
    os.BusinessPartnerName AS supplierName, 
    opoh.zz1bedatpdhpdh AS zz1bedatpdhpdh  
FROM 
    ods_purchase_order_header opoh 
LEFT JOIN 
    ods_supplier os ON os.Supplier = opoh.Supplier  
WHERE 
    opoh.PurchaseOrderType = 'Z012';

第二个 SQL：

SELECT 
    omh.PostingDate  
FROM 
    ods_materialdocument_head omh 
LEFT JOIN 
    ods_materialdocument_item omi ON omh.MaterialDocument = omi.MaterialDocument  
    AND omh.MaterialDocumentYear = omi.MaterialDocumentYear 
LEFT JOIN 
    vmi_materialdocument vmi ON omi.MaterialDocument != vmi.MaterialDocument  
    AND omi.MaterialDocumentYear != vmi.MaterialDocumentYear  
WHERE 
    ( omi.IsAutomaticallyCreated != 'X' OR omi.IsAutomaticallyCreated IS NULL )  
    AND omi.PurchaseOrder IN (  ${purchaseOrders} )  
    AND omi.GoodsMovementType IN ( SELECT gmt.GoodsMovementType FROM goodsmovement gmt )  
LIMIT 0,1;

第二个 SQL 中的 ${purchaseOrders} 等于第一个 SQL 中的 opoh.PurchaseOrder。

合并后的 SQL 查询语句如下：

SELECT 
    opoh.PurchaseOrder AS purchaseorder, 
    opoh.PurchaseOrderType AS purchaseordertype, 
    opoh.Supplier AS supplier, 
    opoh.PurchasingOrganization AS purchasingorganization, 
    opoh.PurchasingGroup AS purchasinggroup, 
    opoh.CompanyCode AS companycode, 
    opoh.PaymentTerms AS paymentterms, 
    opoh.ZZ1_PosttingDate_PDH AS create_time, 
    opoh.ZZ1_PosttingDate_PDH AS zZ1PosttingDatePDH, 
    opoh.ZZ1_PosttingDate_PDH AS posttingdate, 
    opoh.DocumentCurrency AS documentCurrency, 
    os.BusinessPartnerName AS supplierName, 
    opoh.zz1bedatpdhpdh AS zz1bedatpdhpdh, 
    omh.PostingDate AS PostingDate 
FROM 
    ods_purchase_order_header opoh 
LEFT JOIN 
    ods_supplier os ON os.Supplier = opoh.Supplier  
LEFT JOIN 
    ods_materialdocument_item omi ON omi.PurchaseOrder = opoh.PurchaseOrder 
LEFT JOIN 
    ods_materialdocument_head omh ON omh.MaterialDocument = omi.MaterialDocument 
        AND omh.MaterialDocumentYear = omi.MaterialDocumentYear 
LEFT JOIN 
    vmi_materialdocument vmi ON omi.MaterialDocument != vmi.MaterialDocument 
        AND omi.MaterialDocumentYear != vmi.MaterialDocumentYear 
WHERE 
    opoh.PurchaseOrderType = 'Z012' 
    AND (omi.IsAutomaticallyCreated != 'X' OR omi.IsAutomaticallyCreated IS NULL) 
    AND omi.GoodsMovementType IN (SELECT gmt.GoodsMovementType FROM goodsmovement gmt) 
LIMIT 0,1;

在合并后的查询语句中，第二个 SQL 查询结果的 omh.PostingDate 被添加到了第一个 SQL 查询结果中。同时，使用了适当的连接条件将两个查询语句联合起来，确保结果行数与第一个 SQL 查询结果一致。