一比较难的oracle的select写法

[billlyh]

表中有百万计类似上表的数据，其中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

[billlyh]

我试过用分析函数dense_rank,count，并不能完全实现上面三种

[billlyh]

注意: 第2条 employee和user_name 是一对一 第3条 employee和user_name 是一对多

[Emory1949]

筛选逻辑 第一条和第三条都是一对多的关系 怎么条件却不一样？一个都大于 一个都小于？

[Emory1949]

说错了 是：筛选逻辑 第一条和第三条都是一对多的关系 怎么条件却不一样？

[阿三]

--写的比较麻烦，没有考虑太多,请参考
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)

[billlyh]

引用 5 楼 zxf_feng 的回复:

--写的比较麻烦，没有考虑太多,请参考
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)

你的这种写法可以用在游标中吗？？？？？？？？