sql-server - SQL查询 - 如何通过总和数获得最小费用

Question

我在使用 SQL Server 查询时遇到问题，这让我卡住了 2 天。所以，我必须计算符合此条件的最小收费金额:

有 1.8 的尺寸限制，如果超过 1.8，他们将收取额外费用:

Size| Additional Charge Amount
------------------------------
0.3 | $1000
0.6 | $2000
0.9 | $3000
1.2 | $3000

例如:

示例 1:

Size| Additional Charge Amount
------------------------------
0.3 | $1000
0.6 | $0
1.2 | $0

这个案例有 2 个选项:

1. 0.3 和 0.6 加上 1.8，所以 1.2 将收取 $3​​000
2. 0.6 和 1.2 允许 1.8，所以 0.3 将收取 $1000

在这种情况下，最小收费金额为 1000 美元，因此额外收费金额将为 0.3 美元 1000 美元

示例 2:

Size| Additional Charge Amount
------------------------------
0.6 | $0
0.6 | $0
0.6 | $0
1.2 | $3000

这种情况下我们有两个选择:

1. 津贴:第三个 0.6 和最后一个 1.2，因此第一个和第二个数据的大小 0.6 将收取额外费用，结果 $2000 + $2000 = $4000 或
2. 津贴:第一、第二和第三个 0.6，因此尺寸 1.2 将收取额外金额，即 3000 美元。

因为我们要收取最小的金额，所以额外收取的金额为 1.2 码 $3000。

示例 3:

Size| Additional Charge Amount
------------------------------
0.6 | $2000
0.6 | $0
1.2 | $0

这个案例收费$2000，因为0.6和1.2是allowance，所以前0.6要加收。

示例 4:

Size| Additional Charge Amount
------------------------------
0.3 | $0
0.3 | $0
0.6 | $0
0.6 | $0

这种情况不会额外收费。

所以，我的想法是，我必须知道哪个较大的数字总和为 1.8，所以我知道较小的尺寸。因为，尺寸越大，附加费的金额就越贵。但是，如果我有这个逻辑，示例 2 将返回错误的结果 ($4000)。

你们知道如何处理这个案例吗？我尝试了很多方法并搜索了类似的案例，但我一无所获。任何人都可以帮忙吗？

Answer 1

它根据尺码的升序或降序来确定哪些尺码收费，哪些不收费，然后比较总计费金额以确定收费多少。

注意:引入了一个ID来按照要求的方式计算size的累加和。

--  create sample table & data
declare @charge table
(
    size    decimal(4,1),
    charge  int
)

insert into @charge (size, charge)
values (0.3, 1000), (0.6, 2000), (0.9, 3000), (1.2, 3000)

declare @sample1 table
(   
    id  int identity,
    size    decimal(4,1)
)
insert into @sample1 (size)
values (0.3), (0.6), (1.2)

declare @sample2 table
(
    id  int identity,
    size    decimal(4,1)
)
insert into @sample2 (size)
values (0.6), (0.6), (0.6), (1.2)

declare @sample3 table
(
    id  int identity,
    size    decimal(4,1)
)
insert into @sample3 (size)
values (0.6), (0.6), (1.2)

declare @sample4 table
(
    id  int identity,
    size    decimal(4,1)
)
insert into @sample4 (size)
values (0.3), (0.3), (0.6), (0.6)

-- the query
; with 
-- this cte gets the charge for each size. `cumm_size` is cummulative
-- running total for the size in ascending order (a) or descending order (d)
cte as
(
    select  s.size, c.charge,
            cumm_size_a = sum(s.size) over (order by s.size, id),
            cumm_size_d = sum(s.size) over (order by s.size desc, id)
    from    @sample1 s
            inner join @charge c    on  s.size  = c.size
),
-- this cte determine the charge by inspecting the value of `cumm_size`
-- less than 1.8, charge = 0
cte2 as
(
    select  *, 
            charge_a = case when cumm_size_a <= 1.8 then 0 else charge end,
            charge_d = case when cumm_size_d <= 1.8 then 0 else charge end
    from    cte
)
-- last part of the query
-- `sum(charge) over()` will gives you the total charge
-- comparing it between the ascending and descending method and return the lower of both
select  size, 
        charge = case   when    sum(charge_a) over() > sum(charge_d) over()
                        then    charge_d
                        else    charge_a
                        end
from    cte2 c
order by c.size