我有一個場景，我需要匯總 n 個折扣才能獲得總折扣。每個折扣必須扣除前一個折扣。
例如：我有一個 200 盧比的訂單。（金額總和），我有多個代金券。第一張讓我享受 15% 的折扣，200-(200*(15/100)) = 170。然后我們有第二張優惠券，價值 10 盧比，170-(10) = 160。
順序很重要，因此另一個欄位記錄了應用折扣的順序。
下面是表格：
- 命令
id order_id productId amount
1 1 5 160
2 1 9 40
所以沒有折扣的總金額是：200 盧比。
- 折扣
id order_id seq type amt
1 1 1 Per (%) 15
2 1 3 Flat 10
因此，折扣金額將為： ((200*(15/100)))30 10 = 40 。
所以我嘗試用 CTE 撰寫 SQL 查詢，但它沒有給出預期的輸出：
WITH recursive cte_calctotalamount AS
(
SELECT order_id,
sum(amount) AS totalamount
FROM ORDER
WHERE order_id=1
GROUP BY order_id ),
cte_totaldiscountamount AS
(
SELECT i.order_id,
i.seq,
i.amt,
ta.totalamount AS totalamount,
CASE
WHEN i.type='Flat' THEN i.amt
WHEN i.type='Per' THEN (ta.totalamount * (i.amt/100))
END totaldiscountedamount,
(totalamount- (
CASE
WHEN i.type='Flat' THEN i.amt
WHEN i.type='Per' THEN (ta.totalamount * (i.amt/100))
END) ) amountafterdiscount
FROM discount i
INNER JOIN cte_calctotalamount ta
ON ta.order_id=i.order_id
UNION
SELECT d.order_id,
d.seq,
d.amt,
ad.totalamount,
CASE
WHEN d.type='Flat' THEN d.amt
WHEN d.type='Per' THEN (ad.amountafterdiscount - (d.amt/100))
END totaldiscountedamount,
(amountafterdiscount - (
CASE
WHEN d.type='Flat' THEN d.amt
WHEN d.type='Per' THEN (ad.amountafterdiscount - (d.amt/100))
END) ) amountafterdiscount
FROM discount d
INNER JOIN cte_totaldiscountamount ad
ON d.order_id=ad.order_id
AND d.seq=ad.seq 1 )
SELECT *
FROM cte_totaldiscountamount;
請幫助實作以下輸出，
order_id totalAmount totalDiscountedAmount amountAfterDiscount
1 200 40 160
uj5u.com熱心網友回復：
您需要在查詢中修改 4 件事
- 在遞回查詢中，您需要初始化第一個結果集。它是下一次迭代計算的基礎。在這種情況下，您需要在遞回部分添加第一個查詢
where i.seq = 1（我們從初始折扣開始）。 - 其次，您沒有遞回地添加折扣金額。為此，您需要從以前的迭代中檢索行折扣金額。所以而不是：
case
when d.type='Flat' THEN d.amt
WHEN d.type='Per' THEN (ad.AmountAfterDiscount - (d.amt/100))
END totalDiscountedAmount
你應該寫：
totalDiscountedAmount case
when d.type='Flat' THEN d.amt
WHEN d.type='Per' THEN (ad.AmountAfterDiscount - (d.amt/100))
END totalDiscountedAmount
您將需要在折扣中添加新的行增量器。當 Union 之后的第二個查詢回傳的結果集為 null 時，遞回查詢將結束。由于條件
d.seq=ad.seq 1為假，查詢將不回傳任何內容。這是由于在discounts表中，您的下一個序列是 3 而不是 2。在建議的解決方案中，您可以看到它在使用折扣的 CTE 中回傳ROW_NUMBER()最后，您只需要保留最后一行（因為如果 N 是某個訂單的折扣數，遞回查詢將自然回傳 N 行。您可以通過將最后一個輸出與子查詢連接起來簡單地做到這一點，如圖所示這個例子。
您的最終查詢將如下所示：
WITH RECURSIVE CTE_CalcTotalAmount
AS
(
select order_id,sum(amount) As totalAmount from "order"
where order_id=1
group by order_id
),
CTE_DiscountsPerOrder as (
select order_id, seq, amt, type, row_number() over (partition by order_id order by seq asc ) as new_seq from discount ) ,
CTE_TotalDiscountAmount AS
(
select i.order_id,i.new_seq,i.amt,ta.totalAmount as TotalAmount,
case
when i.type='Flat' THEN i.amt
WHEN i.type='Per' THEN (ta.totalAmount * (i.amt/100))
END totalDiscountedAmount,
(totalAmount-
(case
when i.type='Flat' THEN i.amt
WHEN i.type='Per' THEN (ta.totalAmount * (i.amt/100))
END)
) AmountAfterDiscount
from CTE_DiscountsPerOrder i
inner JOIN CTE_CalcTotalAmount ta ON ta.order_id=i.order_id
where i.new_seq=1
UNION
select d.order_id,d.new_seq,d.amt,ad.totalAmount,
totalDiscountedAmount case
when d.type='Flat' THEN d.amt
WHEN d.type='Per' THEN (ad.AmountAfterDiscount - (d.amt/100))
END totalDiscountedAmount,
(AmountAfterDiscount -
(case
when d.type='Flat' THEN d.amt
WHEN d.type='Per' THEN (ad.AmountAfterDiscount - (d.amt/100))
END)
) amountAfterDiscount
From CTE_DiscountsPerOrder d
inner JOIN CTE_TotalDiscountAmount ad on d.order_id=ad.order_id
AND d.new_seq=ad.new_seq 1
)
select * from CTE_TotalDiscountAmount a
join (select order_id, count(*) as totalDiscounts from CTE_DiscountsPerOrder group by 1) b on b.order_id = a.order_id and b.totalDiscounts = a.new_seq;
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/442346.html
標籤：sql PostgreSQL postgresql-9.4