輸入：
CUSTOMER_NAME ORDER_ID STATUS
-----------------------------------------------
john j1 delivered
john j2 delivered
david d1 submitted
david d3 created
smith s1 submitted
krish k1 created
一家比薩餅公司正在接受客戶的訂單，每個比薩餅訂單都作為一個單獨的訂單添加到他們的資料庫中，每個訂單都有一個關聯的狀態“已創建或已提交或已交付”。最終狀態根據狀態計算如下
當客戶的所有訂單的狀態為已交付時，客戶訂單的最終狀態為“已完成”
如果客戶有一些未交付的訂單和一些已交付的訂單，則最終狀態為“進行中”
如果所有客戶訂單都已提交，則最終狀態為“等待進度”
否則最終狀態為“等待提交”
期望的輸出：
CUSTOMER_NAME FINAL_STATUS
--------------------------------------------------
david IN PROGRESS
john COMPLETED
krish AWAITING SUBMISSION
smith AWAITING PROGRESS
uj5u.com熱心網友回復：
這是一種方法：
select customer_name
, case when count(case when status <> 'created' then 1 end) = 0 then 'awaiting submission'
when count(case when status <> 'submitted' then 1 end) = 0 then 'awaiting progress'
when count(case when status <> 'delivered' then 1 end) > 0 then 'In progress'
when count(case when status <> 'delivered' then 1 end) = 0 then 'Completed'
end
from orders
group by customer_name
uj5u.com熱心網友回復：
您可以旋轉結果，例如：
with pivoted as (
SELECT *
FROM orders
pivot(
count(order_id) for status in (
[delivered], [submitted], [created])
)as pv
)
SELECT
p.CUSTOMER_NAME,
CASE
WHEN delivered>0 AND submitted=0 AND created=0 THEN 'COMPLETED'
WHEN delivered>0 AND (submitted>0 OR created>0) THEN 'IN PROGRESS'
WHEN delivered=0 AND submitted>0 AND created=0 THEN 'AWAITING PROGRESS'
ELSE 'AWAITING SUBMISSION'
END AS FINAL_STATUS
FROM pivoted p
但是您應該注意，在您的問題中，David 所需的輸出是 IN PROGRESS，但根據邏輯定義，對于此輸出，他應該有一個已交付的訂單。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/329760.html