mysql sum时去重

mysql sum时去重

前言

在做项目的过程中，总是会遇到一些求和的情况，表A和表B关联之后，导致表A数据重复，但是求和字段中又有表A的某个字段。当然了，最直接的想法就是先根据表A分组之后再进行求和，但是在实际应用中，可能本身原来就是一个比较复杂和耗时的查询，再进行一个分组之后再包装一层，会大大降低效率，也可能会影响其他字段的值。也在网上看了一些博友贴出的sum DISTINCT case when和sum DISTINCT 求和字段 这两种写法，只要遇到求和字段有重复值，就给排除掉了，也参考了其他一些想法，最终写出一个可以解决我问题的一种写法。

最终写法

SELECT SUM(sc.score) AS enSum,
SUM(DISTINCT CONVERT(st.id*POWER(10,10),DECIMAL(30,0))+st.credits)
-SUM(DISTINCT CONVERT(st.id*POWER(10,10),DECIMAL(30,0))) AS creSum
 FROM student st
JOIN stu_score sc ON sc.stu_id  = st.id
WHERE sc.course = '英语';

过程演示

创建表

CREATE TABLE `student`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `sname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '学生姓名',
  `credits` int NOT NULL DEFAULT 0 COMMENT '学分',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '学生表' ROW_FORMAT = Dynamic;

CREATE TABLE `stu_score`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `stu_id` int NOT NULL COMMENT '学生ID',
  `grade` int NOT NULL COMMENT '学年',
  `course` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '课程',
  `score` int NOT NULL DEFAULT 0 COMMENT '分数',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '学生成绩表' ROW_FORMAT = Dynamic;

插入测试数据

INSERT INTO `student` (`sname`, `credits`) VALUES ('张一', 30);
INSERT INTO `student` (`sname`, `credits`) VALUES ('张二', 30);
INSERT INTO `student` (`sname`, `credits`) VALUES ('张三', 32);
INSERT INTO `student` (`sname`, `credits`) VALUES ('张四', 25);

INSERT INTO `stu_score` (`stu_id`, `grade`, `course`, `score`) VALUES (1, 2021, '英语', 88);
INSERT INTO `stu_score` (`stu_id`, `grade`, `course`, `score`) VALUES (1, 2021, '高数', 65);
INSERT INTO `stu_score` (`stu_id`, `grade`, `course`, `score`) VALUES (2, 2021, '英语', 80);
INSERT INTO `stu_score` (`stu_id`, `grade`, `course`, `score`) VALUES (2, 2021, '高数', 78);
INSERT INTO `stu_score` (`stu_id`, `grade`, `course`, `score`) VALUES (3, 2021, '英语', 66);
INSERT INTO `stu_score` (`stu_id`, `grade`, `course`, `score`) VALUES (3, 2021, '高数', 65);
INSERT INTO `stu_score` (`stu_id`, `grade`, `course`, `score`) VALUES (4, 2021, '英语', 80);
INSERT INTO `stu_score` (`stu_id`, `grade`, `course`, `score`) VALUES (4, 2021, '高数', 90);
INSERT INTO `stu_score` (`stu_id`, `grade`, `course`, `score`) VALUES (1, 2022, '英语', 80);
INSERT INTO `stu_score` (`stu_id`, `grade`, `course`, `score`) VALUES (1, 2022, '高数', 65);
INSERT INTO `stu_score` (`stu_id`, `grade`, `course`, `score`) VALUES (2, 2022, '英语', 81);
INSERT INTO `stu_score` (`stu_id`, `grade`, `course`, `score`) VALUES (2, 2022, '高数', 74);
INSERT INTO `stu_score` (`stu_id`, `grade`, `course`, `score`) VALUES (3, 2022, '英语', 66);
INSERT INTO `stu_score` (`stu_id`, `grade`, `course`, `score`) VALUES (3, 2022, '高数', 69);
INSERT INTO `stu_score` (`stu_id`, `grade`, `course`, `score`) VALUES (4, 2022, '英语', 80);
INSERT INTO `stu_score` (`stu_id`, `grade`, `course`, `score`) VALUES (4, 2022, '高数', 91);
INSERT INTO `stu_score` (`stu_id`, `grade`, `course`, `score`) VALUES (1, 2023, '英语', 82);
INSERT INTO `stu_score` (`stu_id`, `grade`, `course`, `score`) VALUES (1, 2023, '高数', 69);
INSERT INTO `stu_score` (`stu_id`, `grade`, `course`, `score`) VALUES (2, 2023, '英语', 80);
INSERT INTO `stu_score` (`stu_id`, `grade`, `course`, `score`) VALUES (2, 2023, '高数', 79);
INSERT INTO `stu_score` (`stu_id`, `grade`, `course`, `score`) VALUES (3, 2023, '英语', 66);
INSERT INTO `stu_score` (`stu_id`, `grade`, `course`, `score`) VALUES (3, 2023, '高数', 66);
INSERT INTO `stu_score` (`stu_id`, `grade`, `course`, `score`) VALUES (4, 2023, '英语', 88);
INSERT INTO `stu_score` (`stu_id`, `grade`, `course`, `score`) VALUES (4, 2023, '高数', 90);

需求阐述

需要查询所有学生的学分合计和所有学生三年的英语成绩合计，不能使用子查询

错误查询1

直接对求和字段DISTINCT，两个30的值只能统计进来一个

SELECT SUM(sc.score) AS enSum,
SUM(DISTINCT st.credits) AS creSum
 FROM student st
JOIN stu_score sc ON sc.stu_id  = st.id
WHERE sc.course = '英语';

错误查询2

sum DISTINCT case when 或 sum DISTINCT if 方式，查询出来结果和第一种方式一致

SELECT SUM(DISTINCT IF(st.id,st.credits,0)) 
FROM student st
JOIN stu_score sc ON sc.stu_id  = st.id
WHERE sc.course = '英语';

改进版本1

SELECT SUM(sc.score) AS enSum,
SUM(DISTINCT st.id+st.credits)-SUM(DISTINCT st.id) AS creSum
FROM student st
JOIN stu_score sc ON sc.stu_id  = st.id
WHERE sc.course = '英语';

乍一看，好像是对的了，别急，我们调整下数据。

UPDATE `student` SET `credits` = 29 WHERE `id` = 2

再次进行查询

我去，不应该是116吗，怎么变成85了，相信很多人已经看出了

改进版本2

求和字段表的主键去乘上一个足够大的数（理论上要大于求和字段的最大值）

乍一看，好像可以了，但是如果st.id比较大的话，也会出现问题

UPDATE `student` SET `id` = 10000000 WHERE `id` = 1;
UPDATE stu_score SET stu_id = 10000000 WHERE stu_id = 1;

这里是因为st.id*POWER(10,10)数值太大，被自动转换成科学计数了，最终获取到的也是错误到的结果

最终版本

将计算结果转换为decimal，可以避免被转换成科学计数

结语

最终的方案完美解决了我的问题，但是不排除其他一些特殊情况也会导致结果不正确，如果大家使用时遇到任何困难，都可以留言联系我，如果我没有及时回复可以邮箱联系我（1334881756@qq.com）