try it ... select tt.empname from ( select e.*, row_number() over(partition by e.date order by e.sal desc) as rn from emp e where e.date between '200601' and '200606' )tt where tt.rn <= 20 group by tt.empno,tt.empname having count(1) = to_number('200606') - to_number('200601') + 1;
select g.empno,empname,g.sal from (select empno,empname,sal from(select empno,empname,sal from emp where sal is not null and to_char(date,'yyyymm')='200601' order by sal desc) where rownum <=20) a, (select empno,empname,sal from(select empno,empname,sal from emp where sal is not null and to_char(date,'yyyymm')='200602' order by sal desc) where rownum <=20) b, (select empno,empname,sal from(select empno,empname,sal from emp where sal is not null and to_char(date,'yyyymm')='200603' order by sal desc) where rownum <=20) c, (select empno,empname,sal from(select empno,empname,sal from emp where sal is not null and to_char(date,'yyyymm')='200604' order by sal desc) where rownum <=20) d, (select empno,empname,sal from(select empno,empname,sal from emp where sal is not null and to_char(date,'yyyymm')='200605' order by sal desc) where rownum <=20) e, (select empno,empname,sal from(select empno,empname,sal from emp where sal is not null and to_char(date,'yyyymm')='200606' order by sal desc) where rownum <=20) f, (select empno,empname,sal from emp) g where a.empno=b.empno and b.empno=c.empno and c.empno=d.empno and d.empno=e.empno and e.empno=f.empno and f.empno=g.empno;不知道对不对。请指点
select tt.empname
from (
select e.*,
row_number() over(partition by e.date order by e.sal desc) as rn
from emp e
where e.date between '200601' and '200606'
)tt
where tt.rn <= 20
group by tt.empno,tt.empname
having count(1) = to_number('200606') - to_number('200601') + 1;
from
(select empno,empname,sal
from(select empno,empname,sal from emp
where sal is not null and to_char(date,'yyyymm')='200601' order by sal desc)
where rownum <=20) a,
(select empno,empname,sal
from(select empno,empname,sal from emp
where sal is not null and to_char(date,'yyyymm')='200602' order by sal desc)
where rownum <=20) b,
(select empno,empname,sal
from(select empno,empname,sal from emp
where sal is not null and to_char(date,'yyyymm')='200603' order by sal desc)
where rownum <=20) c,
(select empno,empname,sal
from(select empno,empname,sal from emp
where sal is not null and to_char(date,'yyyymm')='200604' order by sal desc)
where rownum <=20) d,
(select empno,empname,sal
from(select empno,empname,sal from emp
where sal is not null and to_char(date,'yyyymm')='200605' order by sal desc)
where rownum <=20) e,
(select empno,empname,sal
from(select empno,empname,sal from emp
where sal is not null and to_char(date,'yyyymm')='200606' order by sal desc)
where rownum <=20) f,
(select empno,empname,sal from emp) g
where a.empno=b.empno and b.empno=c.empno and c.empno=d.empno and d.empno=e.empno and e.empno=f.empno and f.empno=g.empno;不知道对不对。请指点