阿里云9.9元云服务器限时购买

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
);    
        

 

posted @ 2023-08-03 12:34  HIIT  阅读(58)  评论(0编辑  收藏  举报
阿里云限时红包 最高 ¥ 2000 云产品通用红包,可叠加官网常规优惠使用