# 一：需求

A表和B表的表结构相同，A表是历史表，B表是增量数据表；想要根据关联条件更新A表中的数据。

# 二：表结构

CREATE TABLE A (
id bigint(20) NOT NULL AUTO_INCREMENT,
bid bigint(20) NOT NULL ,
sid bigint(20) NOT NULL ,
grid bigint(20) NOT NULL ,
age bigint(20) NOT NULL ,
pv bigint(20) NOT NULL ,
uv bigint(20) NOT NULL ,
pay_count bigint(20) NOT NULL,
charge_amount bigint(20) NOT NULL ,
last_pay_count bigint(20) NOT NULL ,
last_charge_amount bigint(20) NOT NULL ,
ftime bigint(20) NOT NULL ,
dtime bigint(20) NOT NULL DEFAULT '0' COMMENT '详细时间(yyyyMMddHH)'
PRIMARY KEY (id,ftime),
KEY IX_FTIME (ftime) USING BTREE,
KEY IX_HTIME (htime) USING BTREE,
KEY IX_DTIME (dtime),
KEY IX_B_F_S_G (bid,ftime,sid,grid),
KEY IX_B_S_G (bid,sid,grid),
KEY IX_B_D_S (bid,dtime,sid)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


# 三：注意

1、只更新A表的部分字段：pv、uv和pay_amount

2、确定唯一一条记录的关联字段是：bid、sid、dtime、qrid

# 四：实现

1、使用inner join实现

UPDATE A as aa
INNER JOIN B as bb ON bb.bid = aa.bid
AND bb.sid=aa.sid
AND bb.grid=aa.grid
SET aa.pv=bb.pv,
aa.uv=bb.uv,
aa.pay_amount=bb.pay_amount
WHERE aa.dtime=?
AND aa.bid=? ;

2、使用exists实现

UPDATE A aa
SET    aa.pv = (SELECT bb.pv
FROM   B bb
WHERE  bb.bid = aa.bid
AND bb.sid = aa.sid
AND bb.grid = aa.grid),
aa.uv = (SELECT bb.uv
FROM   B bb
WHERE  bb.bid = aa.bid
AND bb.sid = aa.sid
AND bb.grid = aa.grid),
aa.pay_amount = (SELECT bb.pay_amount
FROM   B bb
WHERE  bb.bid = aa.bid
AND bb.sid = aa.sid
AND bb.grid = aa.grid)
WHERE  EXISTS (SELECT 1
FROM   B bb
WHERE  bb.bid = aa.bid
AND bb.sid = aa.sid
AND bb.grid = aa.grid)
AND aa.dtime = ?
AND aa.bid = ?;



# 五：参考

07-13 44
04-27 2563

08-30 364
07-31 1097
05-07 113
05-13 814
08-30 3万+
12-10 2624
©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie