table emp_history
{employee_code -->pk
job_start_date -->pk
job_end_date -->pk
job_code}select employee , job_code
from emp_history
where to_date('01-JAN-2003','DD-MON-YYYY')
between job_start_date and job_end_date
{employee_code -->pk
job_start_date -->pk
job_end_date -->pk
job_code}select employee , job_code
from emp_history
where to_date('01-JAN-2003','DD-MON-YYYY')
between job_start_date and job_end_date
张三 经理 2002/08/10
张三 总经理 2003/02/01
李四 销售员 1998/10/10
李四 经理 2002/01/20要取出2002/10/10这个时候的张三和李四的职位。该怎么做??
select name , job
from yourtable
where (name , jobdate) in (select name , max(jobdate) maxdate
from yourtable
where jobstartdate < to_date('10-OCT-2002','DD-MON-YYYY'))
from yourtable
where (name , jobdate) in (select name , max(jobdate) maxdate
from yourtable
where jobstartdate < to_date('10-OCT-2002','DD-MON-YYYY')
group by name
)
select name , job
from yourtable
where (name , jobdate) in (select name , max(jobdate) maxdate
from yourtable
where jobstartdate < to_date('10-OCT-2002','DD-MON-YYYY')
group by name /*it is must */
)