mysql 查询重写_mysql-如何将数据库子查询重写为联接？

我正在尝试将此子查询重写为联接.我已经阅读了关于SO的其他问题,但是无法解决这个问题.

create table job (

emplid int,

effdt date,

title varchar(100),

primary key (emplid, effdt)

);

insert into job set emplid=1, effdt='2010-01-01', title='Programmer';

insert into job set emplid=1, effdt='2011-01-01', title='Programmer I';

insert into job set emplid=1, effdt='2012-01-01', title='Programmer II';

insert into job set emplid=2, effdt='2010-01-01', title='Analyst';

insert into job set emplid=2, effdt='2011-01-01', title='Analyst I';

insert into job set emplid=2, effdt='2012-01-01', title='Analyst II';

#Get each employees current job:

select *

from job a

where a.effdt=

(select max(b.effdt)

from job b

where b.emplid=a.emplid);

结果：

+--------+------------+---------------+

| emplid | effdt | title |

+--------+------------+---------------+

| 1 | 2012-01-01 | Programmer II |

| 2 | 2012-01-01 | Analyst II |

+--------+------------+---------------+

我想将查询重写为联接,而无需子查询.这可能吗？