加入兩個具有不同聲明的查詢

我需要加入兩個不同的查詢以從兩者中獲取一些列。第一個有 CTE。

查詢1：

  WITH StatusChanges AS 
(
   SELECT 
      HistoryID,PartID,Status, DateAdded, 
      rnAsset = ROW_NUMBER() OVER (PARTITION BY PartID ORDER BY DateAdded) 
   FROM aAssetHistory
) SELECT 
   A.isMarketed,A.AssetDescription,A.aPartID,A.SerialNumber,A.PartNumber,
   A.Location,A.Vintage,Ap.Fund,A.ProjectNumber,AP.ProjectName ,
   (
      SELECT Top 1 Disposition 
      FROM aAssetDisposition AD 
      WHERE (A.aPartid = AD.AssetID)) As Disposition, A.LocStatus ,
         CASE 
            WHEN A.LocStatus='In Repair' THEN EstimatedCompletion 
            WHEN A.LocStatus like '%lease%' THEN IIF(ProjectedReDeliveryDate='1/1/1900',LeaseExpiry,ProjectedReDeliveryDate)  
            WHEN A.LocStatus='Stored' OR A.LocStatus='Marketed' THEN SC.DateAdded 
         END 'Available',A.AssetComment, 
         Cast(ISNULL((SELECT Top 1 1 
                      FROM aAssetMarketingDocuments AMDocs 
                      WHERE A.aPartid=AMDocs.aPartid),0) As bit
      ) Attachment 
   FROM aAsset A  
INNER JOIN aProject AP On A.ProjectNumber=AP.ProjectNumber 
LEFT JOIN aAsset ParentA  On A.ParentACPartID=ParentA.aPartid 
LEFT JOIN aEngineShop ES ON A.LocStatus='In Repair' AND ES.PartID=A.aPartid 
LEFT JOIN vLeasedAsset LA ON A.LocStatus like '%lease%' AND LA.aPartID=A.aPartid 
LEFT JOIN (
         SELECT SC1.PartID, SC1.DateAdded,RN= ROW_NUMBER() OVER (PARTITION BY SC1.PartID ORDER BY SC1.DateAdded DESC) 
         FROM StatusChanges SC1 
         LEFT JOIN StatusChanges SC2 ON SC1.PartID=SC2.PartID AND SC2.rnAsset=SC1.rnAsset-1 
         WHERE (SC1.Status<>SC2.Status OR SC2.HistoryID IS NULL)) SC 
ON (A.LocStatus='Stored' OR A.LocStatus='Marketed') AND A.aPartid=SC.PartID AND SC.rn = 1 
WHERE ((A.isMarketed=1 AND (ParentA.isMarketed IS NULL OR ParentA.isMarketed=0)) )

查詢2：

SELECT 
   A.[AssetMarketingID],B.PartID, aCompany.Company 'PotentialCustomer', A.isActive, 
   ISNULL(CAST(Lease AS bit),0) 'Lease', ISNULL(CAST(Exchange AS bit),0) 'Exchange', 
   ISNULL(CAST(Sale AS bit),0) 'Sale' ,DocumentStage,ContactTB.FirstName + ' ' + ContactTB.LastName 'Contact', 
   A.ContactDate,A.Status,B.Amount, A.DateCreated, [Notes] 
FROM aAssetMarketing A 
LEFT JOIN aAssetMarketingAssets B ON A.AssetMarketingID=B.AssetMarketingID 
LEFT JOIN aCompany On [PotentialCustomerID]=COid 
LEFT JOIN aCompanyContact ContactTB ON [MarketingContactID]=ContactID 
LEFT JOIN (
         select AssetMarketingID,max(case when DealStructure = 'Exchange' then 1 ELSE 0 end) Exchange, 
         max(case when DealStructure = 'Lease' then 1 Else 0 end) Lease, 
         max(case when DealStructure = 'Sale' then 1 Else 0 end) Sale 
FROM [aAssetMarketingDealStructure] 
GROUP BY AssetMarketingID) DS     
   ON DS.AssetMarketingID=A.AssetMarketingID 
ORDER BY A.ContactDate DESC

  WITH StatusChanges AS 
(
   SELECT 
      HistoryID,PartID,Status, DateAdded, 
      rnAsset = ROW_NUMBER() OVER (PARTITION BY PartID ORDER BY DateAdded) 
   FROM aAssetHistory
) SELECT 
   A.isMarketed,A.AssetDescription,A.aPartID,A.SerialNumber,A.PartNumber,
   A.Location,A.Vintage,Ap.Fund,A.ProjectNumber,AP.ProjectName ,
   (
      SELECT Top 1 Disposition 
      FROM aAssetDisposition AD 
      WHERE (A.aPartid = AD.AssetID)) As Disposition, A.LocStatus ,
         CASE 
            WHEN A.LocStatus='In Repair' THEN EstimatedCompletion 
            WHEN A.LocStatus like '%lease%' THEN IIF(ProjectedReDeliveryDate='1/1/1900',LeaseExpiry,ProjectedReDeliveryDate)  
            WHEN A.LocStatus='Stored' OR A.LocStatus='Marketed' THEN SC.DateAdded 
         END 'Available',A.AssetComment, 
         Cast(ISNULL((SELECT Top 1 1 
                      FROM aAssetMarketingDocuments AMDocs 
                      WHERE A.aPartid=AMDocs.aPartid),0) As bit
      ) Attachment 
   INTO #tmp
   FROM aAsset A  
INNER JOIN aProject AP On A.ProjectNumber=AP.ProjectNumber 
LEFT JOIN aAsset ParentA  On A.ParentACPartID=ParentA.aPartid 
LEFT JOIN aEngineShop ES ON A.LocStatus='In Repair' AND ES.PartID=A.aPartid 
LEFT JOIN vLeasedAsset LA ON A.LocStatus like '%lease%' AND LA.aPartID=A.aPartid 
LEFT JOIN (
         SELECT SC1.PartID, SC1.DateAdded,RN= ROW_NUMBER() OVER (PARTITION BY SC1.PartID ORDER BY SC1.DateAdded DESC) 
         FROM StatusChanges SC1 
         LEFT JOIN StatusChanges SC2 ON SC1.PartID=SC2.PartID AND SC2.rnAsset=SC1.rnAsset-1 
         WHERE (SC1.Status<>SC2.Status OR SC2.HistoryID IS NULL)) SC 
ON (A.LocStatus='Stored' OR A.LocStatus='Marketed') AND A.aPartid=SC.PartID AND SC.rn = 1 
WHERE ((A.isMarketed=1 AND (ParentA.isMarketed IS NULL OR ParentA.isMarketed=0)) )

查詢2：

SELECT 
   A.[AssetMarketingID],B.PartID, aCompany.Company 'PotentialCustomer', A.isActive, 
   ISNULL(CAST(Lease AS bit),0) 'Lease', ISNULL(CAST(Exchange AS bit),0) 'Exchange', 
   ISNULL(CAST(Sale AS bit),0) 'Sale' ,DocumentStage,ContactTB.FirstName + ' ' + ContactTB.LastName 'Contact', 
   A.ContactDate,A.Status,B.Amount, A.DateCreated, [Notes] 
FROM aAssetMarketing A 
LEFT JOIN aAssetMarketingAssets B ON A.AssetMarketingID=B.AssetMarketingID 
LEFT JOIN aCompany On [PotentialCustomerID]=COid 
LEFT JOIN aCompanyContact ContactTB ON [MarketingContactID]=ContactID 
LEFT JOIN (
         select AssetMarketingID,max(case when DealStructure = 'Exchange' then 1 ELSE 0 end) Exchange, 
         max(case when DealStructure = 'Lease' then 1 Else 0 end) Lease, 
         max(case when DealStructure = 'Sale' then 1 Else 0 end) Sale 
FROM [aAssetMarketingDealStructure] 
JOIN #tmp A ON 
A.SerialNumber, A.PartNumber, Location, A.Fund, Disposition, DocumentStage, Lease, Sale
GROUP BY AssetMarketingID) DS     
   ON DS.AssetMarketingID=A.AssetMarketingID 
ORDER BY A.ContactDate DESC

引用自：https://dba.stackexchange.com/questions/104774