前言: 面试遇到 "关于用一张表的字段更新另一张表的字段" 这个问题,开始以为没什么,随便写了个SQL,后来发现还是有问题的,现在整理下.
整理出来后,我又扩展了一题.
题一: bug 表 : 字段 id, last_updated , id为bug编号(主键), last_updated为最后更新时间...
bug_ history表: 字段 id,bug_id,date_modified id代表修订顺编号，也具有唯一性；bug_id，就是bug编号，同bug表中的id，是具有对应关系的；date_modified是这次修订的提交日期。
id last_updated
1 2013-04-01 00:00:00
2 2013-04-01 00:00:00
3 2013-04-01 00:00:00
4 2013-04-01 00:00:00
5 2013-04-01 00:00:00
6 2017-04-12 12:21:26
7 2017-04-12 12:21:30
-------------------------------------------------
id bug_id date_modified
1 1 2017-03-01 12:17:51
5 2 2017-04-12 12:18:17
6 3 2017-04-12 12:18:24
7 4 2017-03-01 12:18:31
9 5 2017-04-04 12:18:46
需求:将历史表中的date_modified 字段 更新 进 bug 表.
开始我用 update bug b set last_updated=(select date_modified from bug_history h where b.id=h.bug_id) 语句,SQL能执行,但是出现了 id 6 , 7 的 last_updated数据为null了,
1 2017-03-01 12:17:51
2 2017-04-12 12:18:17
3 2017-04-12 12:18:24
4 2017-03-01 12:18:31
5 2017-04-04 12:18:46
6 null
7 null
原因:会对bug表进行全表更新,有对应的id使用历史表的数据,没有则为null...
更正为 :
update bug a
INNER JOIN bug_history b on a.id=b.bug_id
set a.last_updated=b.date_modified;
需求解决....
扩展题:
bug 表 不变;
bug_history 表变为
id bug_id date_modified
1 1 2017-03-01 12:17:51
2 1 2017-04-09 12:18:00
3 1 2017-04-12 12:18:07
4 2 2017-02-01 12:18:12
5 2 2017-04-12 12:18:17
6 3 2017-04-12 12:18:24
7 4 2017-03-01 12:18:31
8 4 2017-04-11 12:18:35
9 5 2017-04-04 12:18:46
10 5 2017-04-12 12:18:50
需求 :用bug_history表每个bug_id的最大id号对应的date_modified字段内容，去更新bug表中对应id的last_updated值。
举例来说，bug_history表bug_id=1的最大id=3，然后id=3对应的date_modified是 2017-04-12 12:18:07，然后就用 2017-04-12 12:18:07去更新bug表中id=1的last_updated字段
SQL: update bug a
INNER JOIN bug_history b on a.id=b.bug_id
set a.last_updated=b.date_modified
where not EXISTS(select 1 from bug_history c where b.bug_id=c.bug_id and b.id<c.id);