SQL sum函数一对多场景，消除笛卡尔积

场景：借还款统计数据，一笔贷款可以对应多笔还款。

贷款表

DROP TABLE IF EXISTS `load_contract`;
CREATE TABLE `load_contract`  (
  `load_id` bigint(0) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '贷款ID',
  `load_amt` decimal(10, 2) UNSIGNED NULL DEFAULT NULL COMMENT '贷款金额',
  `persion` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '贷款人',
  PRIMARY KEY (`load_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of load_contract
-- ----------------------------
INSERT INTO `load_contract` VALUES (5, 50.00, 'zzl');
INSERT INTO `load_contract` VALUES (8, 60.00, 'zzl');
INSERT INTO `load_contract` VALUES (10, 20.00, 'zzy');

还款表

DROP TABLE IF EXISTS `repayment_contract`;
CREATE TABLE `repayment_contract`  (
  `repay_id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '还款ID',
  `repay_amt` decimal(10, 2) UNSIGNED NULL DEFAULT NULL COMMENT '还款金额',
  `load_id` bigint(0) UNSIGNED NOT NULL COMMENT '贷款ID',
  PRIMARY KEY (`repay_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of repayment_contract
-- ----------------------------
INSERT INTO `repayment_contract` VALUES (1, 10.00, 5);
INSERT INTO `repayment_contract` VALUES (2, 10.00, 5);
INSERT INTO `repayment_contract` VALUES (3, 10.00, 5);
INSERT INTO `repayment_contract` VALUES (4, 15.00, 8);
INSERT INTO `repayment_contract` VALUES (5, 20.00, 8);
INSERT INTO `repayment_contract` VALUES (6, 5.00, 10);

其中还款表使用load_id作为外键与贷款表关联。

贷款表数据

SELECT * FROM load_contract lc 

load_id	load_amt	persion
5	50.00	zzl
8	60.00	zzl
10	20.00	zzy

还款表数据

SELECT * FROM repayment_contract rc;

repay_id	repay_amt	load_id
1	10.00	5
2	10.00	5
3	10.00	5
4	15.00	8
5	20.00	8
6	5.00	10

• 场景一：统计某人的总共贷款金额。

SELECT SUM(load_amt) FROM load_contract WHERE persion='zzl';

结果为 110.00

• 场景二：统计某人已还款金额

SELECT
    SUM( rc.repay_amt ) 
FROM
    repayment_contract rc,
    load_contract lc 
WHERE
    lc.load_id = rc.load_id 
    AND lc.persion = 'zzl';

结果为 65.00

• 场景三：关联还款表，统计贷款金额（这种情况先不考虑具体的使用场景）。

    SELECT
    SUM( lc.load_amt ) 
FROM
    repayment_contract rc,
    load_contract lc 
WHERE
    lc.load_id = rc.load_id 
    AND lc.persion = 'zzl';

结果为 270.00
关联情况下，贷款表查询出的实际数据如下，可以看出这种情况下出现了笛卡尔积。

SELECT
     lc.*
FROM
    repayment_contract rc,
    load_contract lc 
WHERE
    lc.load_id = rc.load_id 
    AND lc.persion = 'zzl';

load_id	load_amt	persion
5	50.00	zzl
5	50.00	zzl
5	50.00	zzl
8	60.00	zzl
8	60.00	zzl

load_id为5的贷款对应3条还款，load_id为8的贷款对应2条还款。所以sum(lc.load_amt)的结果270 = 50 * 3 + 60*2。

解决方案，使用 DISTINCT 关键字。

SELECT
    SUM( load_amt ) 
FROM
    ( SELECT DISTINCT lc.* FROM 
    repayment_contract rc, load_contract lc 
    WHERE lc.load_id = rc.load_id AND lc.persion = 'zzl' ) t;
    

结果 110.00

• 场景四：同时统计贷款和还款总额。

SELECT
    SUM( lc.load_amt ),
    SUM( rc.repay_amt ) 
FROM
    repayment_contract rc,
    load_contract lc 
WHERE
    lc.load_id = rc.load_id 
    AND lc.persion = 'zzl';

结果为 270.00 ， 65.00。
跟场景三一样，贷款金额出现了笛卡尔积

这种情况可以分两步走

1. 第一步，使用子查询，查询出每笔贷款对应的还款金额

SELECT
    lc.load_amt ,
    ( SELECT SUM( rc.repay_amt ) FROM repayment_contract rc WHERE
    lc.load_id = rc.load_id )  totalRepayAmt
FROM
    load_contract lc 
WHERE
    lc.persion = 'zzl';

load_amt	totalRepayAmt
50.00	30.00
60.00	35.00

2. 第二步，重新汇总数据

SELECT
    SUM( lc.load_amt ) totalLoadAmt,
    SUM( ( SELECT SUM( rc.repay_amt ) FROM 
    repayment_contract rc WHERE lc.load_id = rc.load_id ) ) totalRepayAmt 
FROM
    load_contract lc 
WHERE
    lc.persion = 'zzl';

totalLoadAmt	totalRepayAmt
110.00	65.00

这种方式可能不是很好理解，换成子查询方式。

SELECT
    SUM( totalLoadAmt ),
    SUM( totalRepayAmt ) 
FROM
    (
    SELECT
        lc.load_amt totalLoadAmt,
        ( SELECT SUM( rc.repay_amt ) FROM repayment_contract rc
         WHERE lc.load_id = rc.load_id ) totalRepayAmt 
    FROM
        load_contract lc 
    WHERE
        lc.persion = 'zzl' 
    ) t;

• 场景五：统计某人贷款余额（还没还的贷款）

SELECT SUM(balanceAmt) totalBalanceAmt FROM (SELECT
 load_amt - ( SELECT SUM( rc.repay_amt ) FROM repayment_contract rc 
 WHERE lc.load_id = rc.load_id ) balanceAmt
FROM
    load_contract lc 
WHERE
    lc.persion = 'zzl') t

结果

totalBalanceAmt
45.00

• 场景六：统计所有人的贷款余额（还没还的贷款）

    
SELECT
    persion,
    SUM( balanceAmt ) totalBalanceAmt 
FROM
    (
    SELECT
        lc.persion persion,
        load_amt - ( SELECT SUM( rc.repay_amt ) FROM repayment_contract rc WHERE lc.load_id = rc.load_id ) balanceAmt 
    FROM
        load_contract lc 
    ) t 
GROUP BY
    t.persion

结果

persion	totalBalanceAmt
zzl	45.00
zzy	15.00