使用大小写和分组函数将文本值替换为具有空值的摘要行

Question

我在这里制作了一个sql代码，我正在尝试弄清楚如何使用大小写和分组函数将文字值"ALL"替换为具有空值的摘要行。我还想过滤以 AZ、MI、OH 结尾的状态的结果。这是编写代码的正确方法吗？

  SELECT AccountDescription AS Account, VendorState, SUM(invoicelineitemamount) as LineItemSum
     CASE
         WHEN ISNULL THEN '*ALL*'
     END
 FROM ap.dbo.Vendors
 JOIN ap.dbo.Invoices
     ON Vendors.VendorID = Invoices.VendorID
 JOIN ap.dbo.invoicelineitems
     ON Invoices.InvoiceID = InvoiceLineItems.InvoiceID
 JOIN ap.dbo.GLAccounts
     ON InvoiceLineItems.AccountNo = GLAccounts.AccountNo
 WHERE VendorState IN ('AZ', 'MI', 'OH')
 GROUP BY AccountDescription, VendorState WITH CUBE

Answer 1

如果列值来自数据，则 GROUPING 函数返回 0，如果列值是由 CUBE 操作生成的 NULL，则返回 1。

您的"带案例选择"语句将如下所示

 Select 
     CASE WHEN   (GROUPING(AccountDescription) = 1) THEN 'ALL'
        ELSE AccountDescription
   END AS Account
   CASE WHEN (GROUPING(VendorState) = 1) THEN 'ALL'
        ELSE VendorState
   END AS VendorState,
   SUM(invoicelineitemamount) as   LineItemSum

Answer 2

首先，WITH CUBE语法将在将来的 SQL Server 版本中删除，不应使用; GROUP BY CUBE()也应该这样做（或者您可能想要ROLLUP）。

其次，将NULL值更改为ALL您可以使用grouping()函数或isnull() 。

第三，如果你的VendorState只有两个字母，那么这个：

WHERE VendorState IN ('AZ','MI', 'OH')

是你想要的，但如果它是一个更长的字符串（如 Portland, OR ），末尾有状态代码，请按照下面的示例代码进行操作：

SELECT 
    CASE WHEN GROUPING(AccountDescription) = 1 THEN 'ALL' ELSE AccountDescription END AS Account,
    ISNULL(AccountDescription, 'ALL') AS Account, 
    ISNULL(VendorState, 'ALL') AS VendorState, 
    SUM(invoicelineitemamount) as LineItemSum
FROM ap.dbo.Vendors
JOIN ap.dbo.Invoices
    ON Vendors.VendorID = Invoices.VendorID
JOIN ap.dbo.invoicelineitems
    ON Invoices.InvoiceID = InvoiceLineItems.InvoiceID
JOIN ap.dbo.GLAccounts
    ON InvoiceLineItems.AccountNo = GLAccounts.AccountNo
--WHERE VendorState IN ('AZ','MI', 'OH')
WHERE 
    VendorState LIKE '%AZ' OR 
    VendorState LIKE '%MI' OR
    VendorState LIKE '%OH' 
GROUP BY CUBE(AccountDescription, VendorState)

有关详细信息，请参阅 GROUP BY 的参考文档。

附带说明一下，您可能希望熟悉表别名，您可以使用这些别名不必重复长表标识符，例如：

FROM ap.dbo.Vendors v
JOIN ap.dbo.Invoices i ON v.VendorID = i.VendorID