使用计数进行SQL查询清理

Question

我有下面的工作查询，但是似乎必须有一种更简单的方法来编写这个查询。我已经尽可能地清理了查询，并将其粘贴到下面，非常感谢您给我的任何帮助/建议。

样本结果：

UserID  | MemberCount | TotalCheck | TotalCCs
---------------------------------------------- 
123     |  75         |  25        | 0 
456     |  74         |  129       | 156

示例查询：

Select BPE.UserID
    ,ISNULL((Select COUNT(*) 
           From clients 
           where fac_id = BPE.billpay_FacID 
               and clt_web_type = 1 
               and clt_relationship = 0),'0') as MemberCount
    ,(Select SUM(achorder_total) as ACHTotal 
        from ACHOrder 
        where achorder_siteid = BPE.siteID 
                    and ACHOrder_PayDate between @Start and @End 
                    and ACHOrder_Status not in ('Voided','Reversed')) As TotalChecks
    ,(Select SUM(CCorder_total) as CCTotal 
        from CCOrder 
        where CCorder_siteid = BPE.siteID 
                    and CCOrder_PayDate between @Start and @End 
                    and CCOrder_Status not in ('Voided','Reversed')) As TotalCCs
From BillingEnabled BPE
Order By BPE.UserID

是否有一种更简单的方法来构造这个查询？

Answer 1

如果我正确理解，您想独立计算/和无关的行集吗？我不认为你在那里有太多选择。

我会使用交叉应用(或它的姐妹外部应用)。它稍微改变了语法，但是它可能不会改变执行计划(不过，您应该检查它)。我只是在猜)。

SELECT BPE.UserID, MemberCount.Value, TotalChecks.Value
FROM BillingEnabled BPE
CROSS APPLY (SELECT COUNT(*) as Value 
             FROM clients 
             WHERE fac_id = BPE.billpay_FacID and ...) MemberCount
OUTER APPLY (SELECT SUM(achorder_total) as Value 
             FROM ACHOrder 
             WHERE achorder_siteid = BPE.siteID AND ...) TotalChecks
ORDER BY 1

如果您先执行完整的计算，然后加入结果，那么执行计划可能是不同的。不确定这是否会更有效或更糟。如果您不查询所有内容，只查询一小部分用户，那么如果不以某种方式限制前两个查询，这将是非常低效率的。

WITH MemberCount AS
(
  SELECT fac_id, COUNT(*) as Value
  FROM clients
  WHERE clt_web_type = 1 AND clt_relationship = 0
  GROUP BY fac_id
),
TotalChecks AS
(
  SELECT achorder_siteid, SUM(achorder_total) as Value
  FROM ACHOrder
  WHERE ACHOrder_PayDate BETWEEN @Start AND @End 
    AND ACHOrder_Status NOT IN ('Voided','Reversed')) 
  GROUP BY achorder_siteid
)
SELECT BPE.UserId, MemberCount.Value, TotalChecks.Value
FROM BPE
LEFT OUTER JOIN MemberCount 
  ON MemberCount.fac_id = BPE.billpay_FacID
LEFT OUTER JOIN TotalChecks
  ON TotalChecks.achorder_siteid = BPE.siteID
ORDER BY 1