不常用的SQL持续记录发布于2024-01-27 23:33:15，更新于2024-01-28 21:36:08，标签：sql 转载随意，文章会持续修订，请注明来源地址：https://meethigher.top/blog

一、单表操作

1.) 更新表中字段A的值为字段B的倍数

update test set money = age * 10

二、多表操作

1.) 从历史表数据提取最新数据汇集到实时表

有两张表，一张表是new_info一张表是history_info，现在需要从history_info中，将每条数据的最新值更新到new_info

方式一：子查询。效率低

update new_info as n set
money = (select money from history_info hi where hi.id=n.id order by hi.time desc limit 1),
age = (select age from history_info hi where hi.id=n.id order by hi.time desc limit 1),
time = (select time from history_info hi where hi.id=n.id order by hi.time desc limit 1)

方式二：多表联动update。效率高

update new_info as n set
money = m.money,
age = m.age 
from (select id,money,age,time from history_info hi where (id,time) in (select id,max(time) from history_info group by id) ) as m
where n.id=m.id