There were three recharge orders, and now we have to refund 450 yuan. How can we allocate them to minimize the number of recharge orders involved in this refund? Refer to the following figure for specific data:
Step 1: arrange the refundable amount in descending order to give priority to orders with large refundable amount
Step 2: use CTE common expression to realize functions similar to for or while loop or cursor
create table #t ( Recharge int, Retired int, Refundable int ) Insert into #t (recharge, refunded, refundable) values (200, 100, 100), (500, 200, 300), (300, 100, 200) /* Author: zhang502219048 Script source: https://www.cnblogs.com/zhang502219048/p/14127208.html */ Declare @i to retreat int = 450; with cte1 as ( select *, row_ Number () over (order by refundable DESC) Rn, 0 to initiate refund, 0 to be refunded from #t ), cte2 as ( Select RN, recharge, refunded, refundable, Refundable = case when @ I want to refund > refundable then refundable else @ I want to refund end, To be refunded = @ I to be refunded - case when @ I to be refunded > refundable then refundable else @ I to be refunded end -- to be refunded = to be refunded - refund can be initiated from cte1 where rn = 1 union all Select t2.rn, T2. Recharge, T2. Refunded, T2. Refundable, Refundable = case when T1. To be refunded > T2. Refundable then T2. Refundable else T1. To be refunded end, To be retired = T1. To be retired - case when T1. To be retired > T2. To be retired then T2. To be retired else T1. To be retired end from cte1 t2 Inner join cte2 T1 on t1.rn = t2.rn - 1 -- T2 is the next record of T1 --Where T2. RN > 1 and T1. To be returned > 0 ) select * from cte2 drop table #t
4、 Script run results
This is the end of this article about SQL Server splitting the total refund amount into as few orders as possible. For more information about splitting the total refund amount of SQL server into orders, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!