SQL查询清理使用COUNT

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

有没有更简单的构建这个查询的方法？

- UserAyeJay

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

- jods