sql-server - 如何进行基于 SQL 范围的计算

Question

我有两列“TotalAmount”和“DefaultFees”

但是有一个更细化的表格显示，“0.010000”和“500.000000”之间的“TotalAmount”（即 837.681561）费用应该是“2.500000”

对于 '500.010000' 和 '800.000000' 之间的总金额的其余部分，即 300 费用应该是 '4.876000'

剩下的37.681561，应该是违约费。Min/Max 表可以有更多行

如何在 SQL 中实现这一点

预期产出

标签： sql-server

Answer 1

尽管 Isaac 的答案更清楚，但作为替代解决方案，您可以使用带有子查询的 case-when 语句，如下所示。

Select TotalAmount,
       case when TotalAmount >= (select max(MaximumAmount) from TableB) then DefaultFees 
            else (Select Fees 
                  From TableB b 
                  where a.TotalAmount<b.MaximumAmount and a.TotalAmount>=b.MinimumAmount
                 )
       end as FinalFee
From TableA a

编辑：

根据您更新的问题，这是您要查找的查询：

CREATE TABLE #TableA (myVal INT, DefaultFee float)
INSERT INTO #TableA VALUES (837,3.66)

CREATE TABLE #TableB (minamount INT, maxamount INT, fees float)
INSERT INTO #TableB VALUES (0,500,2.5),(500,800,4.876)

;WITH cte AS (
    SELECT ISNULL(b.minamount, (SELECT MAX(maxamount) FROM #tableB)) as [max]
    from #TableA a
    left join #TableB b on a.myVal between b.minamount and maxamount
)
SELECT maxamount, fees
FROM #TableB
WHERE maxamount <= (SELECT [max] FROM cte)

UNION

SELECT (a.myval - (SELECT [max] FROM cte)), a.DefaultFee
FROM #TableA a
ORDER BY 1 desc