报告存储过程 - 如何避免重复？

Question

我正在编写一个报告存储过程。我想要获取未确认和未开票的采购订单的数量，并能够（可选）筛选 CustomerID。

我下面的内容按预期工作，但我担心 a) 它很慢 b) WHERE 子句的 CustomerID 部分存在重复。

你会如何编写这个存储过程，Stack Overflow？

SELECT 
  (SELECT COUNT(*)
   FROM PurchaseOrder
   WHERE AcknowledgmentStatus <> 'Complete'
   AND (@CustID = 0 OR CustomerID = @CustID)
  ) AS NonAckd,
  (SELECT COUNT(*)
   FROM PurchaseOrder
   WHERE InvoiceStatus <> 'Complete'
   AND (@CustID = 0 OR CustomerID = @CustID)
  ) AS NonInvoiced

Answer 1

像这样的事情：

SELECT Sum(Case When AcknowledgmentStatus <> 'Complete' Then 1 ELSE 0 END) as NonAckd
      ,Sum(Case When InvoiceStatus <> 'Complete'        Then 1 ELSE 0 END) as NonInvoiced 
  FROM PurchaseOrder 
 WHERE CustomerID = IsNull(NullIf(@CustID, 0), CustomerID) 

Answer 2

我不确定 100% 你想要什么，但你可以按如下方式简化客户部分：（未经[疲倦]内存测试）

set @custID = nullif(@custID,0)

SELECT 
  (SELECT COUNT(*)
   FROM PurchaseOrder
   WHERE AcknowledgmentStatus <> 'Complete'
   AND ( CustomerID = isnull(@CustID,CustomerID) )
  ) AS NonAckd,
  (SELECT COUNT(*)
   FROM PurchaseOrder
   WHERE InvoiceStatus <> 'Complete'
   AND ( CustomerID = isnull(@CustID,CustomerID) )
  ) AS NonInvoiced

Answer 3

您不能使用：

• COUNT 因为它会计算所有行（1 或 0 不为空，因此会计算它们）
• COUNT DISTINCT 将给出 2 （仅值 1 和 0）

如果您删除状态检查，它当然会运行得更快你可以处理零。

SELECT
    CASE WHEN AcknowledgmentStatus <> 'Complete' THEN 1 ELSE 0 END AS NonAckd,
    CASE WHEN InvoiceStatus <> 'Complete' THEN 1 ELSE 0 END AS NonInvoiced,
FROM
    PurchaseOrder
WHERE
    (AcknowledgmentStatus <> 'Complete' OR  InvoiceStatus <> 'Complete') --optional
    AND
    (@CustID = 0 OR CustomerID = @CustID)