mysql处理打卡外勤主键，联合主键约束更新不了数据

本文详细介绍了一套针对ts_clock_emp_grant表的SQL操作流程，包括查询员工代码出现多次的记录，更新特定日期范围内的有效和无效日期，以及通过删除重复的失效时间和使用ID进行去重来保留每名员工最大日期的记录。这些技巧对于处理大量数据并保持数据库的整洁性和效率至关重要。

create table ts_2020_emp_grant as
select * from ts_clock_emp_grant;

---查询大于1条--
select * from ts_clock_emp_grant where EMP_CODE in (
select EMP_CODE from(
select EMP_CODE from ts_clock_emp_grant where INVALID_DATE>='2020-03-01' and INVALID_DATE<='2020-03-29' group by EMP_CODE having count(EMP_CODE) > 1) tmp
)
and INVALID_DATE not in(
select INVALID_DATE from (
select max(INVALID_DATE) as INVALID_DATE from ts_clock_emp_grant where INVALID_DATE>='2020-03-01' and INVALID_DATE<='2020-03-29' group by EMP_CODE having count(EMP_CODE) > 1) tmp
) ;

---更新这个时间段---

update ts_clock_emp_grant set EFFECTIVE_DATE='2020-01-01',INVALID_DATE='2020-03-30' where INVALID_DATE>='2020-03-01' and INVALID_DATE<='2020-03-29';

--保留一条最大的--如果出现2条以上多执行删除重复的失效时间处理去重
delete from ts_clock_emp_grant where EMP_CODE in (
select EMP_CODE from(
select EMP_CODE from ts_clock_emp_grant where INVALID_DATE>='2020-03-01' and INVALID_DATE<='2020-03-29' group by EMP_CODE having count(EMP_CODE) > 1) tmp
)
and INVALID_DATE not in(
select INVALID_DATE from (
select max(INVALID_DATE) as INVALID_DATE from ts_clock_emp_grant where INVALID_DATE>='2020-03-01' and INVALID_DATE<='2020-03-29' group by EMP_CODE having count(EMP_CODE) > 1) tmp
);

--保留一条最大的--如果出现2条以上多执行删除重复的用ID处理去重

这个比较好

select * from ts_clock_emp_grant where EMP_CODE in (
select EMP_CODE from(
select EMP_CODE from ts_clock_emp_grant where INVALID_DATE>='2019-03-01' and INVALID_DATE<='2020-03-29' group by EMP_CODE having count(EMP_CODE) > 1) tmp
)
and id not in(
select id from (
select max(id) as id from ts_clock_emp_grant where INVALID_DATE>='2019-03-01' and INVALID_DATE<='2020-03-29' group by EMP_CODE having count(EMP_CODE) > 1) tmp
) ;.