【MySQL】根据查询结果更新统计值

需求

• 反三范式的情况下，同步冗余列
• 用到分组的技巧 （显式或隐式）

造一个需求：

班级表有一个冗余字段——学生平均年龄；
学生表有班级表的外键class_id；
现在需要根据学生表的数据，同步班级表的冗余字段——学生平均年龄

• student
  
• class
  

文末有建表脚本

使用表连接完成需求

一个通用的步骤去理解需求：

• 识别更新的主表

update class c set student_avg_age where c.class_id = ?
1

• 查原数据

select 
	s.class_id, avg(s.student_age) as temp_student_avg_age 
from 
	student s  GROUP BY s.class_id
1
2
3
4

• 观察源数据，更新的关键信息已收集完毕，如果假设能把表字段映射成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;
1
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
1
2
3
4
5
6
7

值得注意的是，通过表连接，现在数据的主视角是班级表。

• 让班级表完成最后的更新
  这一步很简单，就是把基本的更新语句包在外面

# 型如这样的包装
update xxx
set 
	c.student_avg_age = temp.temp_student_avg_age
where 
	c.class_id = temp.class_id
1
2
3
4
5
6

• 最终的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
1
2
3
4
5
6
7
8
9
10
11
12
13

使用子查询

子查询的语句表现得非常简单:

update class c set c.student_avg_age = (SELECT avg(s.student_age) FROM `student` s where s.class_id = c.class_id);
1

这里没有用上分组，原因是where s.class_id = c.class_id 在起着类似隐性分组的作用

分解来看，就能理解可以省略分组的原因：

1. upddate class_id = 1 的数据，先去查class_id = 1 的平均学生年龄。
2. upddate class_id = 2 的数据，先去查class_id = 2 的平均学生年龄。