我们可以在不进行子查询的情况下对数据进行求和吗？

Question

我希望与利率和他们的差异作为保证金

相关的合同我想这样做总和在SQL Server中没有自我加入子查询中的表。

我的架构设置如下：

合同

ContractId relatedContractId Discriminator 
----------------------------------------------------- 
8041  8040    VendorContract 
8040  8041    CustomerContract 
8042  NULL    CustomerContract 
8043  8044    CustomerContract 
8044  8043    VendorContract 

价格

RateId RatePerUnit ContractId 
--------------------------------- 
8052 120.00  8041 
8053 123.00  8041 
8050 121.00  8040 
8051 127.00  8040 
8052 120.00  8042 
8053 137.00  8042 
8054 102.00  8043 
8055 100.00  8044 

预期成果是

CContract  VContract Marging 
------------------------------------ 
8040   8041   5.00 
8042   Null   257.00 
8043   8044   2.00 

我已经实现了正确的结果，下面的查询使用了子查询，但我需要在不使用子查询的情况下执行此操作。

SELECT cts.ContractId, 
     cts.RelatedContractId, 
     (ISNULL(SUM(r.RatePerUnit),0) - ISNULL(vr.VendorContractRate,0)) AS Margin 
FROM Contracts cts 
     LEFT OUTER JOIN Contracts vcts 
      ON cts.RelatedContractId = vcts.ContractId 
     LEFT OUTER JOIN Rates r 
      ON cts.ContractId = r.ContractId 
     LEFT OUTER JOIN 
     ( SELECT c.ContractId, 
        ISNULL(SUM(r.RatePerUnit),0) VendorContractRate 
      FROM Contracts c 
        LEFT OUTER JOIN Rates r 
         ON r.ContractId = c.contractId 
      WHERE c.Discriminator = 'VendorContract' 
      GROUP BY c.ContractId 
     ) vr 
      ON cts.RelatedContractId = vr.ContractId   
WHERE cts.Discriminator = 'CustomerContract' 
GROUP BY cts.ContractId, cts.RelatedContractId, vr.VendorContractRate; 

我试图重写此查询，而无需使用子查询，如下所示：

SELECT vc.ContractId, 
     SUM(r.RatePerUnit), 
     SUM(vr.RatePerUnit) 
FROM Contracts c 
     LEFT OUTER JOIN Contracts vc 
      ON c.RelatedContractId = vc.ContractId 
     LEFT OUTER JOIN Rates r 
      ON r.ContractId= c.ContractId 
     INNER JOIN Rates vr 
      ON vr.ContractId = vc.ContractId 
WHERE c.Discriminator = 'CustomerContract' 
AND  c.ContractStatus = 0 
GROUP BY vc.ContractId; 

不幸的是，这是我的复制结果，所以它是不正确的。对于多种费率，它将费率乘以存在的费率数量。所以我得到的结果是：

CContract  VContract Marging 
------------------------------------ 
8040   8041   10.00  ---- in this case 496-486(248-243) 
8042   Null   514.00  
8043   8044   2.00   

Answer 1

我看不出这样做没有某种形式的子查询的简单方式，但也没有理由尽量避免子查询。你已经列举了希望避免子查询的性能原因，但这不是一个有效的原因，SQL是一种声明性语言，即你告诉优化者你想要什么而不是如何获得它，所以仅仅因为你使用了子查询并不意味着你是改变计划的必需品。

虽然不需要删除子查询来提高性能，但这并不是说您的查询性能仍然无法提高。这可以通过删除不必要的连接来完成。下面应该有更好的表现：

WITH SummedRates AS 
( SELECT ContractID, RatePerUnit = SUM(RatePerUnit) 
    FROM Rates 
    GROUP BY ContractID 
) 
SELECT c.ContractID, 
     c.RelatedContractID, 
     Margin = ISNULL(r.RatePerUnit, 0) - ISNULL(r2.RatePerUnit, 0) 
FROM Contracts AS c 
     LEFT JOIN SummedRates AS r 
      ON r.ContractId = c.ContractId 
     LEFT JOIN SummedRates AS r2 
      ON r2.ContractId = c.RelatedContractId 
WHERE c.Discriminator = 'CustomerContract' 

FULL工作演示

DECLARE @Contracts TABLE (ContractId INT, relatedContractId INT, Discriminator VARCHAR(20)); 
INSERT @Contracts (ContractId, relatedContractId, Discriminator) 
VALUES 
    (8041, 8040, 'VendorContract'), 
    (8040, 8041, 'CustomerContract'), 
    (8042, NULL, 'CustomerContract'), 
    (8043, 8044, 'CustomerContract'), 
    (8044, 8043, 'VendorContract'); 

DECLARE @Rates TABLE (RateId INT, RatePerUnit DECIMAL(5, 2), ContractId INT); 
INSERT @Rates (RateId, RatePerUnit, ContractId) 
VALUES 
    (8052, 120.00, 8041), 
    (8053, 123.00, 8041), 
    (8050, 121.00, 8040), 
    (8051, 127.00, 8040), 
    (8052, 120.00, 8042), 
    (8053, 137.00, 8042), 
    (8054, 102.00, 8043), 
    (8055, 100.00, 8044); 

WITH SummedRates AS 
( SELECT ContractID, RatePerUnit = SUM(RatePerUnit) 
    FROM @Rates 
    GROUP BY ContractID 
) 
SELECT c.ContractID, 
     c.RelatedContractID, 
     Margin = ISNULL(r.RatePerUnit, 0) - ISNULL(r2.RatePerUnit, 0) 
FROM @Contracts AS c 
     LEFT JOIN SummedRates AS r 
      ON r.ContractId = c.ContractId 
     LEFT JOIN SummedRates AS r2 
      ON r2.ContractId = c.RelatedContractId 
WHERE c.Discriminator = 'CustomerContract';