场景

????????用户信息表ri_building_unit_user中字段key_person_type代表用户是红码还是黄码，fk_building_unit_id字段代表是房屋的编码，统计每一个房屋中红码人员总数，写入房屋表ri_building_unit字段key_person_red_num。用户信息表ri_building_unit_user字段fk_building_unit_id对应房屋表ri_building_unit字段id

解决方案

update ri_building_unit as a inner join (select fk_building_unit_id, count(*) as countnum from ri_building_unit_user where key_person_type = 2 group by fk_building_unit_id) as b on a.id = b.fk_building_unit_id?

set a.key_person_yellow_num= b.countnum

update ri_building_unit as a inner join (select fk_building_unit_id, count(*) as countnum from ri_building_unit_user where key_person_type = 1 group by fk_building_unit_id) as b on a.id = b.fk_building_unit_id?

set a.key_person_red_num = b.countnum

注意：

提供下耗时最大的一个测试例子：

update ri_building_unit as a set key_person_yellow_num=(select count(*) from ri_building_unit_user as b? where a.id = b.fk_building_unit_id and b.key_person_type = 2)?

MySQL:inner join的使用方法