情景一
A表和B表,一对多关系。B表数据只要发生更改,A表的col_a字段,就会被与A表有关联关系的B表的三个字段值的总和,进行重新赋值,即col_a=col_ba+col_bb+col_bc。 表A(user_step_group) +--------+--------+-----------------+ | usg_id | userid | usg_total_steps | +--------+--------+-----------------+ | 1 | 5047 | 46339 | | 2 | 5046 | 0 | +--------+--------+-----------------+ 表B(user_step_group_member) +---------+--------+------------+----------------+-----------------+ | usgm_id | usg_id | usgm_steps | usgm_get_steps | usgm_help_steps | +---------+--------+------------+----------------+-----------------+ | 1 | 1 | 17134 | 500 | 377 | | 2 | 1 | 24754 | 500 | 3074 | +---------+--------+------------+----------------+-----------------+ 触发器 DELIMITER $$ CREATE TRIGGER update_total_step_data AFTER UPDATE ON `user_step_group_member` FOR EACH ROW BEGIN UPDATE `user_step_group` usg, `user_step_group_member` usgm SET usg.usg_total_steps = ( SELECT SUM(usgm.usgm_steps + usgm.usgm_get_steps + usgm.usgm_help_steps) nums FROM `user_step_group_member` usgm WHERE usgm.usg_id = usg.usg_id ) WHERE usgm.usg_id = usg.usg_id; END$$ DELIMITER ;
情景二
A表和B表,一对多关系。B表新增一行数据,A表中的字段col_a,就会被B表中与A表的有关联关系的col_b的总和,重新赋值,即col_a=col_b1+col_b2+col_b3+col_b4+col_b5 表A(user_step_group_member) +---------+--------+--------+------------+----------------+-----------------+ | usgm_id | usg_id | userid | usgm_steps | usgm_get_steps | usgm_help_steps | +---------+--------+--------+------------+----------------+-----------------+ | 1 | 1 | 5047 | 21218 | 500 | 377 | | 2 | 1 | 5046 | 22692 | 500 | 3074 | | 3 | 4 | 945 | 85771 | 0 | 0 | +---------+--------+--------+------------+----------------+-----------------+ 表B(user_step_everyday_log) +---------+---------+--------+----------------+----------------+---------------+ | usel_id | usgm_id | userid | usel_self_step | usel_help_step | usel_get_step | +---------+---------+--------+----------------+----------------+---------------+ | 3 | 2 | 5046 | 4406 | 374 | 0 | | 4 | 1 | 5047 | 3561 | 8377 | 0 | | 5 | 3 | 945 | 12883 | 0 | 0 | +---------+---------+--------+----------------+----------------+---------------+ 触发器 DELIMITER $$ CREATE TRIGGER update_everyday_log_to_group_member_way_update AFTER UPDATE ON `user_step_everyday_log` FOR EACH ROW BEGIN UPDATE `user_step_group_member` usgm, `user_step_everyday_log` usel SET usgm.usgm_steps = ( SELECT SUM(usel.usel_self_step) nums FROM `user_step_everyday_log` usel WHERE usgm.usgm_id = usel.usgm_id ) WHERE usgm.usgm_id = usel.usgm_id; END$$ DELIMITER;
情景三
A表和B表,一对多关系。A表中的字段 col_Aa,col_Ab,col_Ac,分别是B表字段 col_Ba,col_Bb,col_Bc有关联关系的数据总和,当B表新增一行数据,A表的相关数据就会重新计算一下B表的对应字段的总和,重新赋值,即 col_Aa=col_Ba1+col_Ba2+col_Ba3,col_Ab=col_Bb1+col_Bb2+col_Bb3,col_Ac同左。 表A(user_step_group_member) +---------+--------+--------+------------+----------------+-----------------+ | usgm_id | usg_id | userid | usgm_steps | usgm_get_steps | usgm_help_steps | +---------+--------+--------+------------+----------------+-----------------+ | 1 | 1 | 5047 | 21218 | 500 | 377 | | 2 | 1 | 5046 | 22692 | 500 | 3074 | | 3 | 4 | 945 | 85771 | 0 | 0 | +---------+--------+--------+------------+----------------+-----------------+ 表B(user_step_everyday_log) +---------+---------+--------+----------------+----------------+---------------+ | usel_id | usgm_id | userid | usel_self_step | usel_help_step | usel_get_step | +---------+---------+--------+----------------+----------------+---------------+ | 3 | 2 | 5046 | 4406 | 374 | 0 | | 4 | 1 | 5047 | 3561 | 8377 | 0 | | 5 | 3 | 945 | 12883 | 0 | 0 | +---------+---------+--------+----------------+----------------+---------------+ 触发器 DELIMITER $$ CREATE TRIGGER update_everyday_log_to_group_member_way_update AFTER UPDATE ON `user_step_everyday_log` FOR EACH ROW BEGIN UPDATE `user_step_group_member` usgm, `user_step_everyday_log` usel SET usgm.usgm_steps = ( SELECT SUM(usel.usel_self_step) nums FROM `user_step_everyday_log` usel WHERE usgm.usgm_id = usel.usgm_id ), usgm.usgm_get_steps = ( SELECT SUM(usel.usel_get_step) nums FROM `user_step_everyday_log` usel WHERE usgm.usgm_id = usel.usgm_id ), usgm.usgm_help_steps = ( SELECT SUM(usel.usel_help_step) nums FROM `user_step_everyday_log` usel WHERE usgm.usgm_id = usel.usgm_id ) WHERE usgm.usgm_id = usel.usgm_id; END$$ DELIMITER;