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

在这种情况下，我们有 2 个选项：

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的津贴，所以前0.6将收取额外的金额。

示例 4：

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

这种情况不会产生额外费用。

所以，我的想法是，我必须知道哪个更大的数字总和为 1.8，所以我知道小尺寸。因为，尺寸越大，附加费用越贵。但是，如果我有这个逻辑，示例 2 将返回错误的结果（4000 美元）。

你们对如何处理这个案子有任何想法吗？我尝试了很多方法并寻找类似的案例，但我一无所获。任何人都可以帮忙吗？

标签： sql-serversum

Answer 1

它根据大小的升序或降序确定哪些尺寸是收费的，哪些不是收费的，然后比较应收费的总金额以确定哪些是收费的。

注：引入 a以按要求的方式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