0%

面试题-sql批量更新

发表于 更新于 分类于 阅读次数： Valine：

面试题目

根据成绩表的数据统计以后更新学生表的total_score字段(即根据学生选修课程的成绩更新的总分)

表结构和数据语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
create table student
(
    id bigint auto_increment,
    stu_name varchar(32) null comment '学生姓名',
    total_score decimal(5,2) default 0 null comment '总分',
    constraint student_pk
        primary key (id)
)
comment '学生表';

create table course_score
(
    stu_id bigint null comment '学生id',
    course_id bigint null comment '课程id',
    score decimal(5,2) default 0.00 null comment '课程分数'
)
comment '选修课程成绩表';


# 新增学生数据
insert into student(id, stu_name, total_score) value (1, '刘德华', 0);
insert into student(id, stu_name, total_score) value (2, '张学友', 0);
insert into student(id, stu_name, total_score) value (3, '郭富城', 0);

# 新增课程分数数据
insert into course_score(stu_id, course_id, score) VALUE (1, 1, 80);
insert into course_score(stu_id, course_id, score) VALUE (1, 2, 80);
insert into course_score(stu_id, course_id, score) VALUE (1, 3, 80);
insert into course_score(stu_id, course_id, score) VALUE (2, 2, 95);
insert into course_score(stu_id, course_id, score) VALUE (2, 3, 85);
insert into course_score(stu_id, course_id, score) VALUE (3, 1, 100);
insert into course_score(stu_id, course_id, score) VALUE (3, 2, 75);

更新sql

  1. mysql

    1
    2
    3
    4
    5
    6
    7
    8
    update
        student s join (
            select stu_id, sum(score) sums
            from course_score
            group by stu_id
        ) c on s.id = c.stu_id
    set s.total_score = c.sums
    where 1=1; # 因为客户端限制一定要where所以加一个true

  2. oracle(使用update…set…from)

    1
    2
    3
    4
    5
    6
    update s
    set s.total_score = (select sum(score) sums
                        from course_score
                        where stu_id = s.id
                        group by stu_id)
    from student s

修改后student表数据:

id stu_name total_score
1 刘德华 240.00
2 张学友 180.00
3 郭富城 175.00