create table t( empno number(18,0), enddate date, startdate date, comments varchar2(4000) );insert into t(empno,enddate,startdate,comments) values(10,to_date('2005-05-31','yyyy-mm-dd'),to_date('2005-04-01','yyyy-mm-dd'),'备注1'); insert into t(empno,enddate,startdate,comments) values(10,to_date('2006-05-15','yyyy-mm-dd'),to_date('2005-05-15','yyyy-mm-dd'),'备注1'); insert into t(empno,enddate,startdate,comments) values(10,to_date('2008-03-01','yyyy-mm-dd'),to_date('2006-03-01','yyyy-mm-dd'),'备注1'); insert into t(empno,enddate,startdate,comments) values(11,to_date('2007-10-08','yyyy-mm-dd'),to_date('2007-02-01','yyyy-mm-dd'),'备注1'); commit;with a as (select t1.empno from t t1 where exists (select 1 from t t2 where t2.empno=t1.empno and t2.startdate<>t1.startdate and t2.enddate<>t1.enddate and t2.startdate>=t1.startdate and t2.startdate<=t1.enddate ) ) select distinct a.empno from a;
--未测试,仅供参考 select * from emp where empno in (select empno from emp where start_date < any (select end_date from emp) group by empno)
empno number(18,0),
enddate date,
startdate date,
comments varchar2(4000)
);insert into t(empno,enddate,startdate,comments) values(10,to_date('2005-05-31','yyyy-mm-dd'),to_date('2005-04-01','yyyy-mm-dd'),'备注1');
insert into t(empno,enddate,startdate,comments) values(10,to_date('2006-05-15','yyyy-mm-dd'),to_date('2005-05-15','yyyy-mm-dd'),'备注1');
insert into t(empno,enddate,startdate,comments) values(10,to_date('2008-03-01','yyyy-mm-dd'),to_date('2006-03-01','yyyy-mm-dd'),'备注1');
insert into t(empno,enddate,startdate,comments) values(11,to_date('2007-10-08','yyyy-mm-dd'),to_date('2007-02-01','yyyy-mm-dd'),'备注1');
commit;with a as (select t1.empno
from t t1
where exists (select 1 from t t2
where t2.empno=t1.empno
and t2.startdate<>t1.startdate
and t2.enddate<>t1.enddate
and t2.startdate>=t1.startdate
and t2.startdate<=t1.enddate ) )
select distinct a.empno
from a;
--未测试,仅供参考
select *
from emp
where empno in (select empno
from emp
where start_date < any (select end_date from emp)
group by empno)