我正在嘗試使用 Case when 和 Sum 運行 oracle 查詢，它給我一個錯誤，說我缺少正確的括號。
下面的查詢，
SELECT c.Customer_name,
CASE WHEN SUM(status = 'DELIVERED') = count(*) THEN 'A'
WHEN SUM(status = 'DELIVERED') > 0 THEN 'B'
WHEN SUM(status = 'SUBMITTED') > 0 THEN 'C'
ELSE 'No Match'
END AS Match
FROM Customer_Order c
group by c.Customer_name order by c.Customer_name;
將我的查詢更改為如下所示，它說，我缺少關鍵字
SELECT c.Customer_name,
CASE WHEN SUM(c.status) = 'DELIVERED' = (count(c.status)) THEN 'A'
WHEN SUM(c.status) = 'DELIVERED' > 0 THEN 'B'
WHEN SUM(c.status) = 'SUBMITTED' > 0 THEN 'C'
ELSE 'No Match'
END AS Match
FROM Customer_Order c
group by c.Customer_name order by c.Customer_name;
uj5u.com熱心網友回復：
好吧，我想你正在嘗試做的包括計算所有的情況下，status = 'DELIVERED'和status = 'SUBMITTED'，然后比較這些東西來決定結果是什么。但是您不能像status = 'DELIVERED'這樣對條件運算式求和，因此您需要分兩步進行：第一步是從條件中得出一個數字，另一步是將總和與某些內容進行比較。
第 1 步：計算status = 'DELIVERED'您需要狀態為 1 時為 1，狀態DELIVERED為非時為 0（與“已提交”相同）的情況。這樣你就有了要求和的數字。所以我們要運行查詢的資料是：
SELECT *,
CASE WHEN status = 'SUBMITTED' THEN 1 ELSE 0 END AS Is_submitted,
CASE WHEN status = 'DELIVERED' THEN 1 ELSE 0 END AS Is_delivered
FROM Customer_order
第 2 步：現在我們可以對第 1 步的結果使用您的查詢：
SELECT
c.Customer_name,
CASE WHEN SUM(c.delivered) = count(*) THEN 'A'
WHEN SUM(c.delivered) > 0 THEN 'B'
WHEN SUM(c.submitted) > 0 THEN 'C'
ELSE 'No Match'
END AS X
FROM (the query above) as c
GROUP BY c.Customer_name
ORDER BY c.Customer_name;
將步驟 1 代入步驟 2，我們得到實際查詢：
SELECT
c.Customer_name,
-- SUM(c.Is_delivered),
-- SUM(c.Is_submitted),
-- count(*),
CASE WHEN SUM(c.delivered) = count(*) THEN 'A'
WHEN SUM(c.delivered) > 0 THEN 'B'
WHEN SUM(c.submitted) > 0 THEN 'C'
ELSE 'No Match'
END AS X
FROM (
SELECT *,
CASE WHEN status = 'SUBMITTED' THEN 1 ELSE 0 END AS submitted,
CASE WHEN status = 'DELIVERED' THEN 1 ELSE 0 END AS delivered
FROM Customer_order
) as c
GROUP BY c.Customer_name
ORDER BY c.Customer_name;
在這里試試：
https://www.db-fiddle.com/f/mSdREEgU1mT1pvczG2kZgm/1
uj5u.com熱心網友回復：
你似乎在嘗試計算真正的比較；與其他一些語言不同，在 SQL 中，真值與 1 不同，假值不是 0，因此您需要使用CASE運算式將比較轉換為可以求和（或計數）的值。
在這種情況下，您可以使用條件聚合：
SELECT Customer_name,
CASE
WHEN COUNT(CASE status WHEN 'DELIVERED' THEN 1 END) = COUNT(status)
THEN 'A'
WHEN COUNT(CASE status WHEN 'DELIVERED' THEN 1 END) > 0
THEN 'B'
WHEN COUNT(CASE status WHEN 'SUBMITTED' THEN 1 END) > 0
THEN 'C'
ELSE 'No Match'
END AS Match
FROM Customer_Order
group by Customer_name
order by Customer_name;
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/314342.html
標籤：甲骨文