题解 | #总分倒序#

找出学员在exam_id = 1,2,3 三场考试中总分大于260的学员id和总分

CREATE TABLE `exam_stu_score` (
  `id` int NOT NULL AUTO_INCREMENT,
  `exam_id` int DEFAULT NULL,
  `user_id` int DEFAULT NULL,
  `score` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO exam_stu_score (exam_id,user_id,score) VALUES (1,1,100);
INSERT INTO exam_stu_score (exam_id,user_id,score) VALUES (2,1,100);
INSERT INTO exam_stu_score (exam_id,user_id,score) VALUES (3,1,100);
INSERT INTO exam_stu_score (exam_id,user_id,score) VALUES (1,2,100);
INSERT INTO exam_stu_score (exam_id,user_id,score) VALUES (2,2,20);
INSERT INTO exam_stu_score (exam_id,user_id,score) VALUES (3,2,30);
INSERT INTO exam_stu_score (exam_id,user_id,score) VALUES (1,3,100);
INSERT INTO exam_stu_score (exam_id,user_id,score) VALUES (2,3,100);
INSERT INTO exam_stu_score (exam_id,user_id,score) VALUES (3,3,100);
INSERT INTO exam_stu_score (exam_id,user_id,score) VALUES (1,4,20);
INSERT INTO exam_stu_score (exam_id,user_id,score) VALUES (2,4,20);
INSERT INTO exam_stu_score (exam_id,user_id,score) VALUES (3,4,100);
INSERT INTO exam_stu_score (exam_id,user_id,score) VALUES (1,5,100);
INSERT INTO exam_stu_score (exam_id,user_id,score) VALUES (2,5,80);
INSERT INTO exam_stu_score (exam_id,user_id,score) VALUES (3,5,100);
INSERT INTO exam_stu_score (exam_id,user_id,score) VALUES (1,6,100);
INSERT INTO exam_stu_score (exam_id,user_id,score) VALUES (2,6,100);
INSERT INTO exam_stu_score (exam_id,user_id,score) VALUES (4,1,70);
INSERT INTO exam_stu_score (exam_id,user_id,score) VALUES (4,2,100);
INSERT INTO exam_stu_score (exam_id,user_id,score) VALUES (4,3,80);
INSERT INTO exam_stu_score (exam_id,user_id,score) VALUES (4,4,100);
INSERT INTO exam_stu_score (exam_id,user_id,score) VALUES (4,5,100);
INSERT INTO exam_stu_score (exam_id,user_id,score) VALUES (4,6,100);
INSERT INTO exam_stu_score (exam_id,user_id,score) VALUES (4,7,70);

select 
user_id,
score
from 
(select 
user_id,
sum(score) as score
from exam_stu_score
where exam_id in (1,2,3)
group by user_id
) t
where score>260
order by score desc