sql - 如何使用TSQL计算级联销售折扣方案？

考虑一个包含3个折扣程序的表，并指定计算顺序

create table program (programid varchar(5), calc_order tinyint)

go

insert tblprogram(programid, calc_order)
values(prog1, 1)

insert tblprogram(programid, calc_order)
values(prog2, 2)

insert tblprogram(programid, calc_order)
values(prog3, 3)

go

…以及分配给程序的产品表

create table tblproduct(productid varchar(11), programid varchar(5))

go

insert tblproduct(productid, programid)
values(productabc, prog1)
insert tblproduct(productid, programid)
values(productdef, prog2)
insert tblproduct(productid, programid)
values(productxyz, prog3)

go

…以及按产品列出的销售表

create table tblsales(productid varchar(11), salesamount numeric(18,2))

go

insert tblsales(productid, salesamount)
values(productabc, 150)

insert tblsales(productid, salesamount)
values(productdef, 500)

insert tblsales(productid, salesamount)
values(productxyz, 650)

go

属于Prog1的产品的销售享受1%的折扣：
.01*产品销售
或者…
.01*150=1.50
属于Prog2的产品的销售额可享受1%的折扣（销售额减去Prog1之前应用的折扣）：
.01*（产品销售-（.01*产品销售）
或者…
.01*（500-（.01*150）=4.985
对于属于Prog3的产品，销售金额减去（Prog2已经支付的金额减去（Prog1支付的金额））后，将获得2%的折扣：
.02*（产品销售-（.01*（产品销售-（.01*产品销售）））
或者…
.02*（650-（.01*（500-（.01*150）））=12.9003
我想一定有一个简单的方法来做到这一点，而不创建一堆临时表？也许是CTE？？同时，也可以播放任意数量的节目…

最佳答案

这里有一个基于集合的kludge，它只是试图避免一个事实，即光标是解决这个问题的正确方法…（这是非常、非常、非常罕见的情况之一）
我在discount表中添加了一个tblprogram列，这似乎是放置它的正确位置，但是很明显，我并不了解您的用例（因此您将需要相应地调整我的代码）。
我使用ROW_NUMBER()来确保calc_order值从1开始并且是连续的（这样代码以后就可以工作了）。如果order by表中的over()存在关联，calc_order表中的tblprogram应删除任何随机行为。很明显，这只是我的谨慎-你比我更了解你的数据-所以如果这段代码过于防御，直接使用你的calc_order。

select
    ROW_NUMBER() over (order by prg.calc_order, prg.programid) as calc_order,
    s.productid,
    p.programid,
    prg.discount, --  this is the additional column to hold your values
    s.salesamount,
    convert(decimal(7,5),0) as rebate
into #results
from tblsales s
join tblproduct p on p.productid=s.productid
join tblprogram prg on prg.programid=p.programid

declare @calc_order int=1

while @@ROWCOUNT>0 begin

    update r set
        rebate=(r.salesamount-isnull((
                select rebate
                from #results
                where calc_order=@calc_order-1
                ),0))*discount,
        @calc_order+=1
    from #results r
    where r.calc_order=@calc_order

end

select *
from #results

产生：

calc_order  productid   programid  discount  salesamount  rebate
1           productabc  prog1      0.01      150.00       1.50000
2           productdef  prog2      0.01      500.00       4.98500
3           productxyz  prog3      0.02      650.00       12.90030

只是为了避免不可避免的向下投票，建议光标是正确的方式——如果while循环是一个光标，则不需要在select中嵌入update，因为到目前为止，你不可避免地保留了回扣的总数，避免了服务器的工作。我也看不出递归CTE如何避免这种情况。如果你想让我把光标代码扔到这里，在下面放一条消息，我会添加它。

关于sql - 如何使用TSQL计算级联销售折扣方案？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20619405/