如何基于多条件匹配两表计算分层版税金额？

核心逻辑 #

要实现按区间分层计算版税，需先关联两张表，再对每个匹配的分层区间计算对应金额的版税，最后汇总每个销售记录的总版税：

1. 按Store、Year、Category关联SALES和ROYALTIES表，确保每个销售记录匹配对应的分层规则
2. 对每个分层区间，计算该区间内的实际计税金额：取销售金额与区间上限的较小值，减去区间下限，若结果为负则取0（即销售金额低于区间下限时不计入）
3. 用计税金额乘以对应区间的费率，得到该区间的版税
4. 按单个销售记录分组，汇总所有区间的版税得到总金额

SQL实现代码 #

SELECT
    s.Store,
    s.Year,
    s.Category,
    s.SalesAmount,
    SUM(
        GREATEST(LEAST(s.SalesAmount, r.final_value) - r.initial_value, 0)
        * (REPLACE(r.royalties_pct, '%', '') / 100)
    ) AS TotalRoyalty
FROM SALES s
INNER JOIN ROYALTIES r
    ON s.Store = r.Store
    AND s.Year = r.Year
    AND s.Category = r.Category
GROUP BY s.Store, s.Year, s.Category, s.SalesAmount;

代码解释 #

• 表关联：通过INNER JOIN确保只匹配符合Store、Year、Category条件的分层规则
• 计税金额计算：
  • LEAST(s.SalesAmount, r.final_value)：限制计税金额不超过当前区间的上限
  • GREATEST(..., 0)：避免出现负数（当销售金额低于区间下限时，该区间计税金额为0）
• 费率转换：用REPLACE(r.royalties_pct, '%', '') / 100将百分比字符串转为可计算的小数
• 汇总计算：SUM()函数汇总单个销售记录所有匹配区间的版税，得到总版税

验证结果 #

根据提供的测试数据，运行后会得到以下结果：

计算过程说明：

• A店2022年X类（250元）：
  • Slot1：(100-0)*5% = 5
  • Slot2：(200-100)*7% =7
  • Slot3：(250-200)*9% =4.5
  • 总计：5+7+4.5=16.5
• B店2022年Y类（260元）：
  • Slot1：(80-0)*4% =3.2
  • Slot2：(160-80)*6% =4.8
  • Slot3：(240-160)*8% =6.4
  • 总计：3.2+4.8+6.4=14.4（注：现有规则无240元以上的费率，故超出部分不计入）

内容的提问来源于stack exchange，提问作者pofferbacco