一比较难的oracle的select写法

Question

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

Answer 1

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

Answer 2

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