表中有百万计类似上表的数据，其中end_date有可能大于系统当前日期
employee user_name end_date row_id
111 zhansan1 2013-12-5 a001
111 zhansan2 2013-11-9 a002
111 zhansan3 2020-5-5 a003
111 zhansan4 2010-2-4 a004
111 zhansan5 2009-3-3 a005
222 lishi1 2007-5-8 a006
222 lishi2 2011-11-11 a007
222 lishi3 2013-2-6 a008
333 zhansan 2012-4-7 a009
如何进行筛选：
1 一个employee对应多个user_name,并且其中end_date有一笔是大于系统当前日期的：
结果：
employee user_name end_date row_id
111 zhansan1 2013-12-5 a001
111 zhansan2 2013-11-9 a002
111 zhansan3 2020-5-5 a003
111 zhansan4 2010-2-4 a004
111 zhansan5 2009-3-3 a005
2 一个employee对应1个user_name,并且其中end_date都小于系统当前日期的：
employee user_name end_date row_id
333 zhansan 2012-4-7 a009
3 一个employee对应多个user_name,并且其中end_date都小于系统当前日期的：
employee user_name end_date row_id
222 lishi1 2007-5-8 a006
222 lishi2 2011-11-11 a007
222 lishi3 2013-2-6 a008
------解决方案--------------------
--写的比较麻烦，没有考虑太多,请参考
CREATE TABLE tmp_01(employee INT,username VARCHAR2(20),end_date DATE,row_id VARCHAR2(10));
insert into tmp_01 values(111 ,'zhansan1',to_date('2013-12-05','yyyy-mm-dd'),'a001');
insert into tmp_01 values(111 ,'zhansan2',to_date('2013-11-09','yyyy-mm-dd'),'a002');
insert into tmp_01 values(111 ,'zhansan3',to_date('2020-05-05 ','yyyy-mm-dd'),'a003');
insert into tmp_01 values(111 ,'zhansan4',to_date('2010-02-04','yyyy-mm-dd'),'a004');
insert into tmp_01 values(111 ,'zhansan5',to_date('2009-03-03','yyyy-mm-dd'),'a005');
insert into tmp_01 values(222 ,'lishi1',to_date('2007-05-08','yyyy-mm-dd'),'a006');
insert into tmp_01 values(222 ,'lishi2',to_date('2011-11-11','yyyy-mm-dd'),'a007');
insert into tmp_01 values(222 ,'lishi3',to_date('2013-02-06','yyyy-mm-dd'),'a008');
insert into tmp_01 values(333 ,'zhansan',to_date('2012-04-07','yyyy-mm-dd'),'a009');
COMMIT;
--1 一个employee对应多个user_name,并且其中end_date有一笔是大于系统当前日期的：
WITH t1 AS (
SELECT employee,username,end_date,row_id,ROW_NUMBER()OVER(partition BY employee ORDER BY end_date DESC) rid FROM tmp_01
)
SELECT * FROM tmp_01 t2
WHERE EXISTS (SELECT * FROM t1 t
WHERE t.rid=1 AND end_date>SYSDATE AND t.employee=t2.employee)
--个employee对应1个user_name,并且其中end_date都小于系统当前日期的：
WITH t1 AS (
SELECT employee,count(distinct username) cname FROM tmp_01
GROUP BY employee
HAVING count(distinct username)=1
)
SELECT * FROM tmp_01 t2
WHERE EXISTS (SELECT * FROM t1 t
WHERE t.employee=t2.employee)
AND end_date<SYSDATE
--3 一个employee对应多个user_name,并且其中end_date都小于系统当前日期的：
WITH t1 AS (
SELECT employee,username,end_date,row_id,ROW_NUMBER()OVER(partition BY employee ORDER BY end_date DESC) rid FROM tmp_01
)
SELECT * FROM tmp_01 t2
WHERE EXISTS (SELECT * FROM t1 t
WHERE t.rid=1 AND t.end_date<SYSDATE AND t.employee=t2.employee)
AND EXISTS (SELECT * FROM t1 t where t.employee=t2.employee AND t.rid>1)