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