使用 COUNT 进行 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

如果我没理解错的话，你想独立地计算/求和不相关的行集？我不认为你在那里有太多选择。

我会使用的是 CROSS APPLY(或其姐妹 OUTER APPLY)。它稍微改变了语法，但它可能不会对执行计划有太大改变(不过你应该检查一下。我只是在这里猜测)。

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