需求
- 反三范式的情况下,同步冗余列
- 用到分组的技巧 (显式或隐式)
造一个需求:
班级表有一个冗余字段——学生平均年龄;
学生表有班级表的外键class_id;
现在需要根据学生表的数据,同步班级表的冗余字段——学生平均年龄
- student
- class
文末有建表脚本
使用表连接完成需求
一个通用的步骤去理解需求:
- 识别更新的主表
update class c set student_avg_age where c.class_id = ?
- 查原数据
select
s.class_id, avg(s.student_age) as temp_student_avg_age
from
student s GROUP BY s.class_id
- 观察源数据,更新的关键信息已收集完毕,如果假设能把表字段映射成sql语句,需求就实现了
update class c set student_avg_age = 12 where c.class_id = 1;
update class c set student_avg_age = 10 where c.class_id = 2;
- 表连接逼近假设
select c.class_id, temp.temp_student_avg_age from class c join (
select
s.class_id, avg(s.student_age) as temp_student_avg_age
from
student s GROUP BY s.class_id
) temp
on c.class_id = temp.class_id
值得注意的是,通过表连接,现在数据的主视角是班级表。
- 让班级表完成最后的更新
这一步很简单,就是把基本的更新语句包在外面
# 型如这样的包装
update xxx
set
c.student_avg_age = temp.temp_student_avg_age
where
c.class_id = temp.class_id
- 最终的sql语句
# 最终的sql语句
update class c join (
select
s.class_id, avg(s.student_age) as temp_student_avg_age
from
student s GROUP BY s.class_id
) temp
on
c.class_id = temp.class_id
set
c.student_avg_age = temp.temp_student_avg_age
where
c.class_id = temp.class_id
使用子查询
子查询的语句表现得非常简单:
update class c set c.student_avg_age = (SELECT avg(s.student_age) FROM `student` s where s.class_id = c.class_id);
这里没有用上分组,原因是where s.class_id = c.class_id
在起着类似隐性分组的作用
分解来看,就能理解可以省略分组的原因:
upddate class_id = 1
的数据,先去查class_id = 1
的平均学生年龄。upddate class_id = 2
的数据,先去查class_id = 2
的平均学生年龄。