一、原始数据表

tb_origin

二、需要更新的表（日统计数据表）

tn_rtjsj1

三、需求

从原始数据表中，先查询出来当天a001~a007各个指标的max、min、avg，在更新到日统计数据表中

四、mysql语句

/*
	2、对日统计表1（tb_rtjsj1）的更新操作
	
	从原始数据表中获取数据，更新到日统计表1中，
	实现 多行+多列的更新
*/

UPDATE
	tb_rtjsj1,
(
	SELECT
		MAX(a001),MAX(a002),MAX(a003),MAX(a004),MAX(a005),MAX(a006),MAX(a007),
		MIN(a001),MIN(a002),MIN(a003),MIN(a004),MIN(a005),MIN(a006),MIN(a007),
		AVG(a001),AVG(a002),AVG(a003),AVG(a004),AVG(a005),AVG(a006),AVG(a007)
	FROM
		tb_origin
	WHERE 
		post_time 
	LIKE 
		'2021-04-30_________'  /*日期需要动态变换（这里需要9个下划线）*/
)AS b

SET
	tb_rtjsj1.`a001` = CASE tb_rtjsj1.`type`
	WHEN 'avg' THEN b.`MAX(a001)`
	WHEN 'max' THEN b.`MAX(a001)`
	WHEN 'min' THEN b.`MIN(a001)`
	END,
	
	tb_rtjsj1.`a002` = CASE tb_rtjsj1.`type`
	WHEN 'avg' THEN b.`MAX(a002)`
	WHEN 'max' THEN b.`MAX(a002)`
	WHEN 'min' THEN b.`MIN(a002)`
	END,
	
	tb_rtjsj1.`a003` = CASE tb_rtjsj1.`type`
	WHEN 'avg' THEN b.`MAX(a003)`
	WHEN 'max' THEN b.`MAX(a003)`
	WHEN 'min' THEN b.`MIN(a003)`
	END,

	tb_rtjsj1.`a004` = CASE tb_rtjsj1.`type`
	WHEN 'avg' THEN b.`MAX(a004)`
	WHEN 'max' THEN b.`MAX(a004)`
	WHEN 'min' THEN b.`MIN(a004)`
	END,
	
	tb_rtjsj1.`a005` = CASE tb_rtjsj1.`type`
	WHEN 'avg' THEN b.`MAX(a005)`
	WHEN 'max' THEN b.`MAX(a005)`
	WHEN 'min' THEN b.`MIN(a005)`
	END,
	
	tb_rtjsj1.`a006` = CASE tb_rtjsj1.`type`
	WHEN 'avg' THEN b.`MAX(a006)`
	WHEN 'max' THEN b.`MAX(a006)`
	WHEN 'min' THEN b.`MIN(a006)`
	END,
	
	tb_rtjsj1.`a007` = CASE tb_rtjsj1.`type`
	WHEN 'avg' THEN b.`MAX(a007)`
	WHEN 'max' THEN b.`MAX(a007)`
	WHEN 'min' THEN b.`MIN(a007)`
	END
WHERE
	tb_rtjsj1.`type` IN ('avg','max','min');