CASE聲明問題

任何人都可以幫助解決這個錯誤嗎？

SQL：

SELECT 
   InvoiceDate, 
   BillingAddress, 
   BillingCity, 
   Total,

   CASE
     WHEN Total < 2.00 THEN 'Baseline Purchase'
     WHEN Total BETWEEN 2.00 AND 6.99 THEN 'Low Purchase'
     WHEN Total BETWEEN 7.00 AND 15.00 THEN 'Target Purchase'
     ELSE 'Top Performer'
   END AS PurchaseType

FROM 
   invoices
WHERE
 PurchaseType = 'Top Performer'
ORDER BY 
 BillingCity

錯誤：消息 207，級別 16，狀態 1，第 13 行無效的列名稱“PurchaseType”。

此錯誤來自您的WHERE子句，而不是您的CASE表達方式。

WHERE PurchaseType = 'Top Performer'但是，您說PurchaseType不是表中的列，因此查詢優化器不知道如何將其作為搜尋謂詞處理。

有幾種方法可以解決這個問題。一種選擇是將CASE表達式複制/粘貼兩次以同時出現在WHEREandSELECT子句中：

SELECT 
   InvoiceDate, 
   BillingAddress, 
   BillingCity, 
   Total,

   CASE
     WHEN Total < 2.00 THEN 'Baseline Purchase'
     WHEN Total BETWEEN 2.00 AND 6.99 THEN 'Low Purchase'
     WHEN Total BETWEEN 7.00 AND 15.00 THEN 'Target Purchase'
     ELSE 'Top Performer'
   END AS PurchaseType

FROM 
   invoices
WHERE
 CASE
     WHEN Total < 2.00 THEN 'Baseline Purchase'
     WHEN Total BETWEEN 2.00 AND 6.99 THEN 'Low Purchase'
     WHEN Total BETWEEN 7.00 AND 15.00 THEN 'Target Purchase'
     ELSE 'Top Performer'
   END = 'Top Performer'
ORDER BY 
 BillingCity

這有點“噁心”，因為你在重複自己，如果你改變那個CASE表達，你需要在兩個地方改變它。

您可以將子句中的邏輯簡化為WHERE甚至不需要 use CASE，因為您要過濾掉除 “Top Performers” 之外的任何內容：

SELECT 
   InvoiceDate, 
   BillingAddress, 
   BillingCity, 
   Total,

   CASE
     WHEN Total < 2.00 THEN 'Baseline Purchase'
     WHEN Total BETWEEN 2.00 AND 6.99 THEN 'Low Purchase'
     WHEN Total BETWEEN 7.00 AND 15.00 THEN 'Target Purchase'
     ELSE 'Top Performer'
   END AS PurchaseType

FROM 
   invoices
WHERE Total > 15.00
ORDER BY 
 BillingCity

或者，您可以將查詢彈出到 CTE 中，然後引用 CTE 來執行過濾。這將允許優化器確定您想要計算CASE表達式，然後過濾它的輸出：

WITH InvoiceData AS (
   SELECT 
       InvoiceDate, 
       BillingAddress, 
       BillingCity, 
       Total,

       CASE
         WHEN Total < 2.00 THEN 'Baseline Purchase'
         WHEN Total BETWEEN 2.00 AND 6.99 THEN 'Low Purchase'
         WHEN Total BETWEEN 7.00 AND 15.00 THEN 'Target Purchase'
         ELSE 'Top Performer'
       END AS PurchaseType

   FROM 
       invoices
)
SELECT *
FROM InvoiceData
WHERE
 PurchaseType = 'Top Performer'
ORDER BY 
 BillingCity

編輯：由於我們沒有表模式，上述查詢都假設Total列被定義為類似DECIMAL(10,2) NOT NULL. 如果該列允許NULL值或比 2 位更精確的值，則CASE語句本身將需要額外的更改。感謝ypercubeᵀᴹ指出我最初沒有提到這些額外的潛在陷阱。

由於 SQL 的邏輯操作順序（WHERE在之前計算SELECT），您不能SELECT在fromWHERE子句中引用計算值。

@AMtwo提供了一些選項。我認為更清潔的解決方案是將其放入CROSS APPLY (VALUES. 一旦你這樣做了，你可以在後面的任何條款中引用它。

SELECT 
   i.InvoiceDate, 
   i.BillingAddress, 
   i.BillingCity, 
   i.Total,
   v.PurchaseType

FROM 
   invoices i
CROSS APPLY (VALUES (
   CASE
     WHEN i.Total < 2.00 THEN 'Baseline Purchase'
     WHEN i.Total >= 2.00 AND i.Total < 7.00 THEN 'Low Purchase'
     WHEN i.Total >= 7.00 AND i.Total < 15.00 THEN 'Target Purchase'
     ELSE 'Top Performer'
   END
) ) v(PurchaseType)
WHERE
 v.PurchaseType = 'Top Performer'
ORDER BY 
 BillingCity;

顯然，您可以通過刪除無論如何都不匹配正確的情況來簡化此特定查詢，Total但這展示了該技術背後的原理。

另請注意，這BETWEEN可能會導致某些值從裂縫中消失。通常最好使用半開區間>= AND <。

引用自：https://dba.stackexchange.com/questions/306069