获取不带UNION的两个数据集的结果

Question

我正在编写一个存储过程，以便从Microsoft Great Plains检索购买的产品，以便对其进行处理。

Great Plains有两套发票表注解。SOP10100/SOP10200包含发票题头和尚未过帐的发票的详细信息。SOP30200/SOP30300包含发票标题和已过帐发票的详细信息。当购买产品时，它立即进入SOP10100/SOP10200，一旦发布，它就从SOP10100/SOP10200移动到SOP30200/SOP30300。有一个公共表格SOP10106，其中包含用户定义的字段，可以连接到任一表格集合。

当拉取购买时，我们需要检查这两个表，以防发票在处理之前过帐。到目前为止，我们能够实现这一点的唯一方法是通过UNION，这基本上是两个相同的查询，不同之处在于一个连接到SOP10100/SOP10200，另一个连接到SOP30200/SOP30300。

我希望找到一种方法，我们可以将其缩减为一个查询(利用共享的SOP10106表)，希望得到一个结果集，该结果集只包含具有SOP10100/SOP10200或SOP30200/SOP30300记录的记录。到GP表的连接在OPENQUERY函数中。

SPROC如下所示：

SELECT [Account1].AccountID, [Account1].AccountNumber AS 'Order_OrgID', [Account2].AccountNumber AS 'CI_OrgID', [Contact].cncy_CustomerNumber AS 'CustomerID', [Contact].EMailAddress1 AS 'Email', [Contact].FirstName, [Contact].LastName, [StringMapMarket].Value AS 'Market', 
GP.Invoice, GP.SKU, MAP.Name, GP.OrderDate, GP.Quantity, GP.SLPRSNID, RTRIM(GP.SKU) + '-' + [StringMapMarket].Value COLLATE DATABASE_DEFAULT as AdjSku, MAP.CourseID, MAP.SubscriptionID
FROM OPENQUERY(GPSERVER, 'SELECT [SOP10100].SOPNUMBE AS Invoice, [SOP10200].ITEMNMBR AS SKU, [SOP10200].ITEMDESC AS Product, [SOP10100].CREATDDT AS OrderDate, [SOP10200].QTYORDER AS Quantity,
               [SOP10100].SLPRSNID, [SOP10100].CUSTNMBR, [SOP10106].USERDEF1, [SOP10100].SOPTYPE, [SOP10100].VOIDSTTS
               FROM CPI.dbo.SOP10100 WITH (NOLOCK)
               INNER JOIN CPI.dbo.SOP10200 WITH (NOLOCK)  ON [SOP10100].SOPNUMBE = [SOP10200].SOPNUMBE AND [SOP10100].CREATDDT > DATEADD(ww,-4,GETDATE())
               INNER JOIN CPI.dbo.SOP10106 WITH (NOLOCK)  ON [SOP10100].SOPNUMBE = [SOP10106].SOPNUMBE WHERE [SOP10200].SOPTYPE = 3
               AND [SOP10100].VOIDSTTS <> 1
               AND NOT [SOP10106].USERDEF1 = ''INTERNAL LMS''') as GP
INNER JOIN [CRMSERVER].CPI_MSCRM.dbo.Account AS [Account1] WITH(NOLOCK) ON GP.CUSTNMBR = [Account1].AccountNumber COLLATE DATABASE_DEFAULT
INNER JOIN [CRMSERVER].CPI_MSCRM.dbo.Contact WITH(NOLOCK) ON GP.USERDEF1 = [Contact].cncy_customernumber COLLATE DATABASE_DEFAULT
INNER JOIN [CRMSERVER].CPI_MSCRM.dbo.Account AS [Account2] WITH(NOLOCK) ON [Contact].ParentCustomerId = [Account2].Id
INNER JOIN CPIProcessData.dbo.StringMap AS [StringMapMarket] WITH(NOLOCK) ON [Account1].cncy_MarketType = [StringMapMarket].AttributeValue AND [StringMapMarket].AttributeName = 'cncy_MarketType' AND [StringMapMarket].ObjectTypeCode = 2
INNER JOIN CPIProcessData.dbo.StringMap AS [StringMapCountry] WITH(NOLOCK) ON [Contact].cncy_address1country = [StringMapCountry].AttributeValue AND [StringMapCountry].AttributeName = 'cncy_address1country' AND [StringMapMarket].ObjectTypeCode = 2
INNER JOIN CPIProcessData.dbo.vwLmsMapping AS MAP WITH(NOLOCK) ON MAP.Subscription = 'true' AND 
                    CASE 
                        WHEN [StringMapCountry].Value IN ('US','Canada') THEN RTRIM(GP.SKU) + '-' + 
                            CASE
                               WHEN [StringMapMarket].Value IN ('HE','MH') THEN 'HE'
                               WHEN [StringMapMarket].Value IN ('ED') THEN 'ED'
                               ELSE 'HS'
                            END
                        ELSE RTRIM(GP.SKU) 
                    END = map.SKU
LEFT OUTER JOIN SeatLog WITH(NOLOCK) ON GP.Invoice = [SeatLog].Invoice AND RTRIM(GP.SKU) = [SeatLog].SKU
WHERE GP.SOPTYPE = 3
AND GP.VOIDSTTS <> 1
AND NOT GP.USERDEF1 = 'INTERNAL LMS'
AND [SubscriptionLog].ID IS NULL

UNION

SELECT [Account1].AccountID, [Account1].AccountNumber AS 'Order_OrgID', [Account2].AccountNumber AS 'CI_OrgID', [Contact].cncy_CustomerNumber AS 'CustomerID', [Contact].EMailAddress1 AS 'Email', [Contact].FirstName, [Contact].LastName, [StringMapMarket].Value AS 'Market', 
GP.Invoice, GP.SKU, MAP.Name, GP.OrderDate, GP.Quantity, GP.SLPRSNID, RTRIM(GP.SKU) + '-' + [StringMapMarket].Value COLLATE DATABASE_DEFAULT as AdjSku, MAP.CourseID, MAP.SubscriptionID
FROM OPENQUERY(GPSERVER, 'SELECT [SOP30200].SOPNUMBE AS Invoice, [SOP30300].ITEMNMBR AS SKU, [SOP30300].ITEMDESC AS Product, [SOP30200].CREATDDT AS OrderDate, [SOP30300].QTYORDER AS Quantity,
               [SOP30200].SLPRSNID, [SOP30200].CUSTNMBR, [SOP10106].USERDEF1, [SOP30200].SOPTYPE, [SOP30200].VOIDSTTS
               FROM CPI.dbo.SOP30200 WITH (NOLOCK)
               INNER JOIN CPI.dbo.SOP30300 WITH (NOLOCK)  ON [SOP30200].SOPNUMBE = [SOP30300].SOPNUMBE AND [SOP30200].CREATDDT > DATEADD(ww,-4,GETDATE())
               INNER JOIN CPI.dbo.SOP10106 WITH (NOLOCK)  ON [SOP30200].SOPNUMBE = [SOP10106].SOPNUMBE WHERE [SOP30300].SOPTYPE = 3
               AND [SOP30200].VOIDSTTS <> 1
               AND NOT [SOP10106].USERDEF1 = ''INTERNAL LMS''') as GP
INNER JOIN [CRMSERVER].CPI_MSCRM.dbo.Account AS [Account1] WITH(NOLOCK) ON GP.CUSTNMBR = [Account1].AccountNumber COLLATE DATABASE_DEFAULT
INNER JOIN [CRMSERVER].CPI_MSCRM.dbo.Contact WITH(NOLOCK) ON GP.USERDEF1 = [Contact].cncy_customernumber COLLATE DATABASE_DEFAULT
INNER JOIN [CRMSERVER].CPI_MSCRM.dbo.Account AS [Account2] WITH(NOLOCK) ON [Contact].ParentCustomerId = [Account2].Id
INNER JOIN CPIProcessData.dbo.StringMap AS [StringMapMarket] WITH(NOLOCK) ON [Account1].cncy_MarketType = [StringMapMarket].AttributeValue AND [StringMapMarket].AttributeName = 'cncy_MarketType' AND [StringMapMarket].ObjectTypeCode = 2
INNER JOIN CPIProcessData.dbo.StringMap AS [StringMapCountry] WITH(NOLOCK) ON [Contact].cncy_address1country = [StringMapCountry].AttributeValue AND [StringMapCountry].AttributeName = 'cncy_address1country' AND [StringMapMarket].ObjectTypeCode = 2
INNER JOIN CPIProcessData.dbo.vwLmsMapping AS MAP WITH(NOLOCK) ON MAP.Subscription = 'true' AND 
                    CASE 
                        WHEN [StringMapCountry].Value IN ('US','Canada') THEN RTRIM(GP.SKU) + '-' + 
                            CASE
                               WHEN [StringMapMarket].Value IN ('HE','MH') THEN 'HE'
                               WHEN [StringMapMarket].Value IN ('ED') THEN 'ED'
                               ELSE 'HS'
                            END
                        ELSE RTRIM(GP.SKU) 
                    END = map.SKU
LEFT OUTER JOIN SeatLog WITH(NOLOCK) ON GP.Invoice = [SeatLog].Invoice AND RTRIM(GP.SKU) = [SeatLog].SKU
WHERE GP.SOPTYPE = 3
AND GP.VOIDSTTS <> 1
AND NOT GP.USERDEF1 = 'INTERNAL LMS'
AND [SubscriptionLog].ID IS NULL

有没有人知道可以在不使用UNION的情况下简化这一过程的想法？我希望能提高性能。

Answer 1

我尝试了一种不同的方法，使用完全连接和合并，如下所示：

SELECT COALESCE(Table1.SOPNUMBE, Table2.SOPNUMBE) AS 'SOPNUMBE', COALESCE(Table1.ITEMNMBR, Table2.ITEMNMBR) AS 'SKU', COALESCE(Table1.CUSTNMBR, Table2.CUSTNMBR) AS 'Account_Number', [SubscriptionLog].ID
FROM (SELECT SOP10100.SOPNUMBE,SOP10200.ITEMNMBR,SOP10100.CUSTNMBR FROM [GPSERVER].CPI.dbo.SOP10100 INNER JOIN [GPSERVER].CPI.dbo.SOP10200 ON SOP10100.SOPNUMBE = SOP10200.SOPNUMBE) AS Table1
FULL JOIN (SELECT SOP30200.SOPNUMBE,SOP30300.ITEMNMBR,SOP30200.CUSTNMBR FROM [GPSERVER].CPI.dbo.SOP30200 INNER JOIN [GPSERVER].CPI.dbo.SOP30300 ON SOP30200.SOPNUMBE = SOP30300.SOPNUMBE) AS Table2 ON Table1.SOPNUMBE = Table2.SOPNUMBE AND Table1.ITEMNMBR = Table2.ITEMNMBR AND Table1.CUSTNMBR = Table2.CUSTNMBR
INNER JOIN [GPSERVER].CPI.dbo.SOP10106 WITH(NOLOCK) ON COALESCE(Table1.SOPNUMBE, Table2.SOPNUMBE) = SOP10106.SOPNUMBE
INNER JOIN [CRMSERVER].CPI_MSCRM.dbo.Account AS [Account1] WITH(NOLOCK) ON COALESCE(Table1.CUSTNMBR, Table2.CUSTNMBR) = [Account1].AccountNumber COLLATE DATABASE_DEFAULT
INNER JOIN [CRMSERVER].CPI_MSCRM.dbo.Contact WITH(NOLOCK) ON SOP10106.USERDEF1 = [Contact].cncy_customernumber COLLATE DATABASE_DEFAULT
--INNER JOIN [CRMSERVER].CPI_MSCRM.dbo.Account AS [Account2] WITH(NOLOCK) ON [Contact].ParentCustomerId = [Account2].Id
INNER JOIN CPIProcessData.dbo.StringMap AS [StringMapMarket] WITH(NOLOCK) ON [Account1].cncy_MarketType = [StringMapMarket].AttributeValue AND [StringMapMarket].AttributeName = 'cncy_MarketType' AND [StringMapMarket].ObjectTypeCode = 2
INNER JOIN CPIProcessData.dbo.StringMap AS [StringMapCountry] WITH(NOLOCK) ON [Contact].cncy_address1country = [StringMapCountry].AttributeValue AND [StringMapCountry].AttributeName = 'cncy_address1country' AND [StringMapMarket].ObjectTypeCode = 2
INNER JOIN CPIProcessData.dbo.vwLmsMapping AS MAP WITH(NOLOCK) ON MAP.Subscription = 'true' AND 
                        CASE 
                            WHEN [StringMapCountry].Value IN ('US','Canada') THEN RTRIM(COALESCE(Table1.ITEMNMBR, Table2.ITEMNMBR)) + '-' + 
                                CASE
                                   WHEN [StringMapMarket].Value IN ('HE','MH') THEN 'HE'
                                   WHEN [StringMapMarket].Value IN ('ED') THEN 'ED'
                                   ELSE 'HS'
                                END
                            ELSE RTRIM(COALESCE(Table1.ITEMNMBR, Table2.ITEMNMBR)) 
                        END = map.SKU
LEFT OUTER JOIN [SubscriptionLog] WITH(NOLOCK) ON SOP10106.SOPNUMBE = [SubscriptionLog].Invoice AND RTRIM(COALESCE(Table1.ITEMNMBR, Table2.ITEMNMBR)) = [SubscriptionLog].SKU
WHERE [SubscriptionLog].ID IS NULL

它似乎运行得更好一些，但它返回重复的结果，而不是我期望的结果。