结转佣金数据库设计：MySQL下佣金累计满额兑付场景如何处理

1. 基础表结构设计 #

首先对月度佣金汇总表做字段扩展，完整示例表结构如下：

CREATE TABLE `commission_monthly` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` int UNSIGNED NOT NULL COMMENT '对应佣金所属用户ID',
  `settle_month` char(7) NOT NULL COMMENT '结算月份，格式为yyyy-MM',
  `current_commission` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '当月实际生成的佣金金额',
  `last_month_carryover` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '上月结转过来的待兑付佣金',
  `total_available` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '当月可兑付总金额=上月结转+当月佣金',
  `actual_paid` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '当月实际兑付金额',
  `next_month_carryover` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '当月结转至下月的待兑付佣金',
  `settle_status` tinyint NOT NULL DEFAULT '0' COMMENT '结算状态：0=未结算 1=已结算',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_month` (`user_id`, `settle_month`),
  KEY `idx_settle_month` (`settle_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '月度佣金汇总表';

2. 月度结算核心执行逻辑 #

每月完成当月佣金数据生成后，按顺序执行以下两个更新语句即可完成结转和兑付判断，无需额外业务代码处理：

步骤1：关联上月数据，更新当月结转金额和可兑付总额 #

UPDATE commission_monthly curr
LEFT JOIN commission_monthly prev 
  ON curr.user_id = prev.user_id
  -- 关联上一个月的结算记录
  AND prev.settle_month = DATE_FORMAT(DATE_SUB(STR_TO_DATE(curr.settle_month, '%Y-%m'), INTERVAL 1 MONTH), '%Y-%m')
SET 
  curr.last_month_carryover = IFNULL(prev.next_month_carryover, 0),
  curr.total_available = curr.last_month_carryover + curr.current_commission
WHERE 
  curr.settle_month = '2024-05' -- 替换为实际需要结算的月份
  AND curr.settle_status = 0;

步骤2：根据兑付门槛计算实际兑付金额和下月结转金额 #

UPDATE commission_monthly
SET 
  actual_paid = IF(total_available >= 50, total_available, 0),
  next_month_carryover = IF(total_available >= 50, 0, total_available),
  settle_status = 1
WHERE 
  settle_month = '2024-05' -- 替换为实际需要结算的月份
  AND settle_status = 0;

3. 临时查询场景实现 #

如果不需要固化结转字段，只想临时查询用户当前累计可兑付金额，可直接用如下查询语句实现：

SELECT 
  user_id,
  SUM(
    CASE WHEN total_available < 50 THEN total_available 
    ELSE 0 END
  ) AS total_pending_commission
FROM commission_monthly
WHERE settle_status = 1
GROUP BY user_id
-- 过滤出已经满足兑付条件的用户
HAVING total_pending_commission >= 50;

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