MySQL语句判断数据库数据重复情况,新增、删除、不变。
判断 7月8月两个月数据对比情况,新增、删除(离职)、重复。
根据manager_name,gg_name,employer,department,historical_office判断出是否重复数据
-- ●- 新增或离职 -- ●- 创建临时表 CREATE TABLE temp_table ( SELECT id,manager_name,gg_name,employer,department,historical_office,create_time,update_time FROM `organization_executive_position` GROUP BY manager_name,gg_name,employer,department,historical_office HAVING COUNT(*) =1 ); select * from temp_table; -- ●- 离职人员 UPDATE `organization_executive_position` t1 right join temp_table t2 on t1.id=t2.id SET t1.zhuangtai = '1' WHERE t1.create_time < '2023-08-01 00:00:00' -- ●- 新增人员 UPDATE `organization_executive_position` t1 right join temp_table t2 on t1.id=t2.id SET t1.zhuangtai = '2' WHERE t1.create_time > '2023-08-01 00:00:00' -- 删除临时表 DROP TABLE temp_table; -- ●- 删除重复数据 DELETE FROM `organization_executive_position` WHERE id NOT IN ( SELECT t.max_id FROM ( -- 查询出ID最大的保留下来 SELECT MAX(id) AS max_id FROM `organization_executive_position` GROUP BY manager_name,gg_name,employer,department,historical_office ) t );